Joined: 06 Sep 2007 Posts: 788 Location: Chennai, India
Is it possible to have both primary key and foreign key from one single table?
Here it is,
Create a table named DSN8910.DEPT ...
CREATE TABLE DSN8910.DEPT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6) ,
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16) ,
PRIMARY KEY(DEPTNO) )
Define ADMRDEPT as the foreign key of a self-referencing constraint on DSN8910.DEPT.
ALTER TABLE DSN8910.DEPT
FOREIGN KEY(ADMRDEPT) REFERENCES DSN8910.DEPT ON DELETE CASCADE;
A table can be a dependent of itself; this is called a self-referencing table. For example, the DEPT table is self-referencing because the value of the administrative department (ADMRDEPT) must be a department ID (DEPTNO). To enforce the self-referencing constraint, DB2 requires that a foreign key be defined.