Constraints interdependent tables

Question

I created 2 tables which are dependent on each other like this.

CREATE TABLE A(NO1 NUMBER(2) PRIMARY KEY,NO2 NUMBER(2));

Table created.

CREATE TABLE B(NO1 NUMBER(2) PRIMARY KEY,NO2 NUMBER(2));

Table created.

ALTER TABLE A ADD CONSTRAINT AA FOREIGN KEY(NO2) REFERENCES B(NO1);

Table altered.

ALTER TABLE B ADD CONSTRAINT BB FOREIGN KEY(NO2) REFERENCES B(NO1);

Table altered.

INSERT INTO A VALUES(10,20);
INSERT INTO A VALUES(10,20);

ERROR at line 1: ORA-02291: integrity constraint (SUBK.AA) violated - parent key not found

INSERT INTO B VALUES(10,20);
INSERT INTO B VALUES(10,20);

ERROR at line 1: ORA-02291: integrity constraint (SUBK.BB) violated - parent key not found

How to insert data in table a and b

Why would you want to create such constraints in the first place? It's impossible to insert data into more than one table at the time, so if both your tables have foreign keys to each other, you can't insert any new data to them at all... — Zohar Peled, Jul 25 '17 at 11:54
I assume `BB` is meant to reference `A(NO1)` rather than `B` - because otherwise, B seems to be independent of `A` and you just need to do a multi-row insert into it first. — Damien_The_Unbeliever, Jul 25 '17 at 12:34
I am not sure I agree this is a duplicate. The referenced question and its accepted answer are about whether such relationships are appropriate. Here, the OP is asking how to manage such a relationship. I believe a reasonable answer is to use deferred constraints, which does not appear as an answer to the question to which this is marked as a duplicate. — Matthew McPeak, Jul 25 '17 at 16:46

score 1 · Answer 1 · answered Jul 25 '17 at 13:01

1

You are asking for troubles ;-), but You can do this in two steps:

insert into A values(10, null); 
insert into B values(20, null);

update A set no2 = 20 where no1 = 10; 
update B set no2 = 10 where no1 = 20;

answered Jul 25 '17 at 13:01

Ponder Stibbons

14,723
2
21
24

Ori Marko · Answer 2 · 2017-07-25T11:26:49.143

0

You create an invalid constraints:

 ALTER TABLE B ADD CONSTRAINT BB FOREIGN KEY(NO2) REFERENCES B(NO1);

Foreign key should be to different table, not to column in the same table.

If you want circular constraint A -> B and B ->A you can insert first data and then add constraints. your foreign key should direct to primary key, so new SQL will be :

CREATE TABLE A(NO1 NUMBER(2) PRIMARY KEY,NO2 NUMBER(2));    

CREATE TABLE B(NO1 NUMBER(2) PRIMARY KEY,NO2 NUMBER(2));

INSERT INTO A VALUES(10,20); 

INSERT INTO B VALUES(10,20); 
ALTER TABLE A ADD CONSTRAINT AA FOREIGN KEY(NO1) REFERENCES B(NO1);

ALTER TABLE B ADD CONSTRAINT BB FOREIGN KEY(NO1) REFERENCES A(NO1);

edited Jul 25 '17 at 11:26

answered Jul 25 '17 at 11:14

Ori Marko

56,308
23
131
233

ALTER TABLE B ADD CONSTRAINT BB FOREIGN KEY(NO2) REFERENCES A(NO1); Even if i change the constraint to the different table. I could not able to insert the data – Radha Sri Seshu Kolla Jul 25 '17 at 11:20
See solution updated – Ori Marko Jul 25 '17 at 11:30

Matthew McPeak · Answer 3 · 2017-07-25T13:09:05.130

Make your constraints deferred. Doing so will cause them to not be enforced until the transaction commits.

Under this setup, you can insert records into both tables without getting an error, as long as both inserts happen in the same transaction.

CREATE TABLE A(NO1 NUMBER(2) PRIMARY KEY,NO2 NUMBER(2));
CREATE TABLE B(NO1 NUMBER(2) PRIMARY KEY,NO2 NUMBER(2));

ALTER TABLE A ADD CONSTRAINT 
   AA FOREIGN KEY(NO2) REFERENCES B(NO1) INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE B ADD CONSTRAINT 
   BB FOREIGN KEY(NO2) REFERENCES A(NO1) INITIALLY DEFERRED DEFERRABLE;

INSERT INTO A VALUES(10,20);
INSERT INTO B VALUES(20,10);

COMMIT;

NOTE: I assumed you wanted the BB constraint to refer to table A, so I changed it.

Constraints interdependent tables

3 Answers3