|
View previous topic :: View next topic
|
| Author |
Message |
shreejita
New User
Joined: 16 Aug 2009
Posts: 60
Location: chennai
|
|
|
|
Hi
Question-Can anybody explain What is the self-referencing constraint?
i understand the following answer
Self-referencing constraint makes the table both the parent
and dependent table in the same referential constraint. The
foreign key to the table is defined after the table is
created.
Can anybody throw some logic of creating this???
Thanks in advance
shreejita |
|
| Back to top |
|
 |
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
| Back to top |
|
|
shreejita
New User
Joined: 16 Aug 2009
Posts: 60
Location: chennai
|
|
|
|
Hi Srihari,
Appreciate your answer but my question is
Is it possible to have both primary key and foreign key from one single table?
Thanks |
|
| Back to top |
|
|
Gnanas N
Active Member
Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India
|
|
|
|
Hi,
| Quote: |
| Is it possible to have both primary key and foreign key from one single table? |
Yes.
Here it is,
Create a table named DSN8910.DEPT ...
| Code: |
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) )
IN DSN8D91A.DSN8S91D; |
Define ADMRDEPT as the foreign key of a self-referencing constraint on DSN8910.DEPT.
| Code: |
ALTER TABLE DSN8910.DEPT
FOREIGN KEY(ADMRDEPT) REFERENCES DSN8910.DEPT ON DELETE CASCADE; |
From manual,
| Quote: |
| 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. |
I got them from,
publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sql_createtable.htm
publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sql_altertable.htm
Please let me know if I'm wrong. |
|
| Back to top |
|
|
|
|
