More on Primary and Foreign Keys:
Remember the order database that I created. Now I am going to establish keys within that
database. First, I am going to make the itemno the primary key for the inven table. I
decided to use a constraint name rather than have the system generate one, so you can see
the format that I used. I also showed the describe after making the change and then showed
the constraints associated with the table INVEN (note: when I did not capitalize inven, I
did not get a return).
SQL CODE:
SQL> select * from inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 48 51.99 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
6789 BAT/BALL 14.99 21.99
7890 Mother Goose 15.25 16.99
10 rows selected.
SQL> desc inven;
Name Null? Type
------------------------------- -------- ----
ITEMNO VARCHAR2(4)
ITEMNAME VARCHAR2(15)
ONHAND NUMBER(5)
ONORDER NUMBER(5)
REORDPT NUMBER(5)
COST NUMBER(6,2)
PRICE NUMBER(6,2)
DEPT CHAR(2)
ITEMCLASS CHAR(2)
LOCATION VARCHAR2(4)
SQL> ALTER TABLE inven
2 ADD CONSTRAINT itemno_pk PRIMARY KEY(itemno);
Table altered.
SQL> Desc inven;
Name Null? Type
------------------------------- -------- ----
ITEMNO NOT NULL VARCHAR2(4)
ITEMNAME VARCHAR2(15)
ONHAND NUMBER(5)
ONORDER NUMBER(5)
REORDPT NUMBER(5)
COST NUMBER(6,2)
PRICE NUMBER(6,2)
DEPT CHAR(2)
ITEMCLASS CHAR(2)
LOCATION VARCHAR2(4)
SQL> SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'INVEN';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------ ------------------------------ --------- --------
SCOTT ITEMNO_PK P INVEN
ENABLED
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'INVEN';
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
INVEN ITEMNO_PK
Before I can make the dept in the table inven a foreign key into the department table, I need
to go in and establish the dept in department table as the primary key for that table.
Remember, when I add a foreign key I tell the field that is acting as a foreign key and the
table that it links into. The foreign key will then link to the primary key in that table.
SQL CODE:
SQL> desc department;
Name Null? Type
------------------------------- -------- ----
DEPT CHAR(2)
DEPTNAME VARCHAR2(15)
MANAGER VARCHAR2(15)
SQL> ALTER TABLE department
2 ADD CONSTRAINT dept_pk PRIMARY KEY(dept);
Table altered.
SQL> desc department;
Name Null? Type
------------------------------- -------- ----
DEPT NOT NULL CHAR(2)
DEPTNAME VARCHAR2(15)
MANAGER VARCHAR2(15)
SQL> SELECT * FROM department;
DE DEPTNAME MANAGER
-- --------------- ---------------
BK Books Jennifer Moore
SP Sports Stephen Willis
TY Toys Carl Baker
Now that department has a primary key based on dept, I can go back and establish the foreign
key in inven that will link into dept.
SQL CODE:
SQL> ALTER TABLE inven
2 ADD CONSTRAINT dept_inven_fk FOREIGN KEY(dept) REFERENCES department
3 ;
Table altered.
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'INVEN';
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
INVEN ITEMNO_PK
INVEN DEPT_INVEN_FK
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'DEPARTMENT';
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
DEPARTMENT DEPT_PK
To test this, I am now going to try to add a row to inven with a dept code that does not appear
in the department table. The row is rejected because of a violation in the foreign key link to
the department table. Specifically the parent was not found in the foreign key table.
SQL CODE:
SQL> INSERT INTO inven
2 VALUES('4567', 'Nancy Drew', 3,4,5,14,16.99,'BO','BK','X100');
INSERT INTO inven
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.DEPT_INVEN_FK) violated - parent key not found
In the next code, I am making the custid the primary key of the table invcust. I will then go
on and make the salsrep the primary key of the table salsrep so that I can make slsrep in the
invcust a foreign key linked to the salsrep primary key (which is also named salsrep).
Remember, if I do not establish the primary key in the table salsrep first, I cannot link a
foreign key into it. The name after REFERENCES refers to the table not the column/field and
is looking for the primary key in that table. Finally, I will show the constraints that were
created in the invcust and salsrep tables.
SQL CODE:
SQL> ALTER TABLE invcust
2 ADD CONSTRAINT custid_pk PRIMARY KEY(custid);
Table altered.
SQL> DESC invcust;
Name Null? Type
------------------------------- -------- ----
CUSTID NOT NULL VARCHAR2(5)
CUSTNAME VARCHAR2(20)
STADR VARCHAR2(15)
APT VARCHAR2(5)
CITY VARCHAR2(15)
STATE CHAR(2)
ZIP VARCHAR2(5)
PASTDUE NUMBER(6,2)
CURRDUE NUMBER(6,2)
CRLIMIT NUMBER(6,2)
DATEFST DATE
SLSREP VARCHAR2(4)
SQL> ALTER TABLE salsrep
2 ADD CONSTRAINT salsrep_pk PRIMARY KEY(salsrep);
Table altered.
SQL> DESC salsrep;
Name Null? Type
------------------------------- -------- ----
SALSREP NOT NULL VARCHAR2(4)
SALSNAME VARCHAR2(20)
COMMRATE NUMBER(3,2)
SQL> ALTER TABLE invcust
2 ADD CONSTRAINT slsrep_fk FOREIGN KEY(slsrep) REFERENCES salsrep;
Table altered.
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS
2 WHERE TABLE_NAME = 'INVCUST' OR TABLE_NAME = 'SALSREP';
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
INVCUST CUSTID_PK
INVCUST SLSREP_FK
SALSREP SALSREP_PK
Now I am going into the order header table, which is called ORDERZ, and establishing the ordno
as the primary key and the custid as a foreign key. Clearly I do not want to accept an order
that is not for a valid customer.
SQL CODE:
SQL> ALTER TABLE ORDERZ
2 ADD CONSTRAINT ordno_pk PRIMARY KEY(ordno);
Table altered.
SQL> DESC ORDERZ;
Name Null? Type
------------------------------- -------- ----
ORDNO NOT NULL VARCHAR2(6)
CUSTID VARCHAR2(5)
ORDATE DATE
SQL> ALTER TABLE orderz
2 ADD CONSTRAINT custid_fk FOREIGN KEY(custid) references invcust;
Table altered.
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS
2 WHERE TABLE_NAME = 'ORDERZ' OR TABLE_NAME = 'INVCUST';
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
INVCUST CUSTID_PK
INVCUST SLSREP_FK
ORDERZ ORDNO_PK
ORDERZ CUSTID_FK
The ordline table has a primary key that is composed of the ordno and the itemno. This is done
by listing the first followed by a comma and then the second within the parenthesis after
primary key. In addition, both ordno and itemno are foreign keys and I want assurance that the
ordno exists on the orderz table and the itemno exists on the inven table.
SQL CODE:
SQL> ALTER TABLE ordline
2 ADD CONSTRAINT ordno_itemno_pk PRIMARY KEY(ordno,itemno)
3 ;
Table altered.
SQL> DESC ordline;
Name Null? Type
------------------------------- -------- ----
ORDNO NOT NULL VARCHAR2(6)
ITEMNO NOT NULL VARCHAR2(4)
NUMORD NUMBER(3)
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS
2 WHERE TABLE_NAME = 'ORDLINE';
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
ORDLINE ORDNO_ITEMNO_PK
SQL> ALTER TABLE ordline
2 ADD CONSTRAINT ordno_fk FOREIGN KEY(ordno) REFERENCES orderz;
Table altered.
SQL> ALTER TABLE ordline
2 ADD CONSTRAINT itemno_fk FOREIGN KEY(itemno) REFERENCES inven;
Table altered.
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS
2 WHERE TABLE_NAME = 'ORDLINE';
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
ORDLINE ORDNO_ITEMNO_PK
ORDLINE ORDNO_FK
ORDLINE ITEMNO_FK
In the following example, I have tried to add an order that is not for a valid customer. The
order is rejected. Then I add an order with a valid customer and the order is accepted.
SQL CODE:
SQL> INSERT INTO orderz
2 VALUES ('000004','22222',sysdate);
INSERT INTO orderz
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CUSTID_FK) violated - parent key not found
SQL> INSERT INTO orderz
2 VALUES('000004','11111',sysdate);
1 row created.
SQL> SELECT * FROM orderz;
ORDNO CUSTI ORDATE
------ ----- ---------
000001 11111 10-JUN-99
000002 12121 10-JUN-99
000003 12345 10-JUN-99
000004 11111 08-JUL-99
Now I am going to add line items for the new order in the ordline table. The first time I am
using an ordno that does not exist in the orderz table and a custid that does not exist in the
invcust table. The second time I am using an ordno and a custid that do exist in the parent
tables and the insert is accepted.
SQL CODE:
SQL> INSERT INTO ordline
2 VALUES ('000005','1414',3);
INSERT INTO ordline
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.ITEMNO_FK) violated - parent key not found
SQL> INSERT INTO ordline
2 VALUES('000004','1212',3);
1 row created.
SQL> SELECT * FROM ordline;
ORDNO ITEM NUMORD
------ ---- ---------
000001 1212 1
000001 2121 1
000001 2345 1
000002 1111 3
000002 3333 1
000003 3333 2
000003 3456 1
000004 1212 3
8 rows selected.
In the examples below, first I try to insert a line item into the ordline table that has the
same primary key as an existing row. The insert is rejected because of a unique constraint
violation. Next I insert a second line item for order 000004 into the ordline and it is
successful. Remember the primary key for the ordline file is the ordno and the itemno
concatenated together. This means I can duplicate the ordno number and in fact I can
duplicate the itemno but I cannot duplicate the ordno and itemno together.
SQL CODE:
SQL> INSERT INTO ordline
2 VALUES('000004','1212',2);
INSERT INTO ordline
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.ORDNO_ITEMNO_PK) violated
SQL> INSERT INTO ordline
2 VALUES('000004','1234',2);
1 row created.
SQL> SELECT * FROM ordline;
ORDNO ITEM NUMORD
------ ---- ---------
000001 1212 1
000001 2121 1
000001 2345 1
000002 1111 3
000002 3333 1
000003 3333 2
000003 3456 1
000004 1212 3
000004 1234 2
9 rows selected.