Is it Possible to Create Self Referencing Foreign Key in Same Table using SQL | Oracle | Primary Key
How to Create Self Referencing Foreign Key in Same Table using SQL | Oracle | Primary Key
You create a self-referencing foreign key just like you do a regular foreign key, except that the referenced column (or columns) is on the same table where you’re defining the foreign key. See the below example:
CREATE TABLE OurStuff(
StuffID Number PRIMARY KEY,
StuffSubID Number ,
StuffName VARCHAR2(10) ,
CONSTRAINT fk_StuffID FOREIGN KEY (StuffSubID)
REFERENCES OurStuff(StuffID));
INSERT INTO OurStuff VALUES (1001, NULL, 'stuff1');
INSERT INTO OurStuff VALUES (1002, 1001, 'stuff2');
INSERT INTO OurStuff VALUES (1003, 1002, 'stuff3');
Till here no issue. Because the 2nd column (StuffSubID) which is a FK has the same value as exists in PK (StuffID). But if we try to insert any such records where StuffSubID has the value which is not exists in StuffID column, it gives same error parent key not found.
INSERT INTO OurStuff VALUES (1004, 1005, 'stuff4');