INTEGRITY CONSTRAINT IN SQL

SQL> CREATE TABLE PERSONS18(PID INTEGER PRIMARY KEY, NAME CHAR(30), MOBILE NUMBER(10));

Table created.

SQL> DESC PERSONS18

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PID                                       NOT NULL NUMBER(38)

 NAME                                               CHAR(30)

 MOBILE                                             NUMBER(10)

  1  CREATE TABLE ORDERS18(OID INTEGER PRIMARY KEY , ORDERNO NUMBER(7) NOT NULL,  PID INTEGER,

  2  CONSTRAINT FKPOR FOREIGN KEY (PID)

  3*  REFERENCES PERSONS18(PID))

  4  ;

Table created.

SQL> DESC PERSONS18

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PID                                       NOT NULL NUMBER(38)

 NAME                                               CHAR(30)

 MOBILE                                             NUMBER(10)

SQL> DESC ORDERS18

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 OID                                       NOT NULL NUMBER(38)

 ORDERNO                                   NOT NULL NUMBER(7)

 PID                                                NUMBER(38)

SQL> INSERT INTO PERSONS18 VALUES(1,'SANKET',8745698547);

1 row created.

SQL> INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB);

Enter value for id: 2

Enter value for name: SANKET1

Enter value for mob: 8745965874

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(2,'SANKET1',8745965874)

1 row created.

SQL> /

Enter value for id: 3

Enter value for name: SANKET2

Enter value for mob: 8742154478

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(3,'SANKET2',8742154478)

1 row created.

SQL> /

Enter value for id: 4

Enter value for name: SANKET3

Enter value for mob: 9854784587

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(4,'SANKET3',9854784587)

1 row created.

SQL> /

Enter value for id: 5

Enter value for name: SANKET4

Enter value for mob: 7488745877

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(5,'SANKET4',7488745877)

1 row created.

SQL> ;

  1* INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM PERSONS18;

       PID NAME                               MOBILE

---------- ------------------------------ ----------

         1 SANKET                         8745698547

         2 SANKET1                         8745965874

         3 SANKET2                         8742154478

         4 SANKET3                       9854784587

         5 SANKET4                          7488745877

SQL> INSERT INTO ORDERS18 VALUES(1,101,1);

1 row created.

SQL> INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID);

Enter value for oid: 2

Enter value for ono: 102

Enter value for pid: 2

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(2,102,2)

1 row created.

SQL> /

Enter value for oid: 3

Enter value for ono: 103

Enter value for pid: 3

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(3,103,3)

1 row created.

SQL> /

Enter value for oid: 4

Enter value for ono: 104

Enter value for pid: 4

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(4,104,4)

1 row created.

SQL> /

Enter value for oid: 5

Enter value for ono: 105

Enter value for pid: 5

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(5,105,5)

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM PERSONS18;

       PID NAME                               MOBILE

---------- ------------------------------ ----------

         1 SANKET                         8745698547

         2 SANKET1                         8745965874

         3 SANKET2                         8742154478

         4 SANKET3                       9854784587

         5 SANKET4                          7488745877

SQL> SELECT * FROM ORDERS18;

       OID    ORDERNO        PID

---------- ---------- ----------

         1        101          1

         2        102          2

         3        103          3

         4        104          4

         5        105          5

  1  SELECT P.NAME,

  2  P.MOBILE,

  3  O.ORDERNO

  4   FROM PERSONS18 P, ORDERS18 O

  5* WHERE P.PID=O.PID

  6  ;

NAME                               MOBILE    ORDERNO

------------------------------ ---------- ----------

SANKET                         8745698547        101

SANKET1                        8745965874        102

SANKET2                        8742154478        103

SANKET3                        9854784587        104

SANKET4                        7488745877        105

SQL> DELETE FROM ORDERS18 WHERE PID=1;

1 row deleted.

SQL> SELECT * FROM ORDERS18;

       OID    ORDERNO        PID

---------- ---------- ----------

         2        102          2

         3        103          3

         4        104          4

         5        105          5

SQL> SELECT * FROM PERSONS18;

       PID NAME                               MOBILE

---------- ------------------------------ ----------

         1 SANKET                         8745698547

         2 SANKET1                        8745965874

         3 SANKET2                        8742154478

         4 SANKET3                        9854784587

         5 SANKET4                        7488745877

SQL> DELETE FROM PERSONS18 WHERE PID=2;

DELETE FROM PERSONS18 WHERE PID=2

*

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.FKPOR) violated - child record found

SQL> ED

Wrote file afiedt.buf

  1* DELETE FROM PERSONS18 WHERE PID=1

  2  ;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL>                                                                                               

;

  1* COMMIT

SQL> COMMIT;

Commit complete.