Preparation
SQL>
create table A (id number, soid number);
SQL>
create table B (id number);
Action
SQL>
alter table A
add constraint SOID_B_FK
foreign key (soid)
references B (id);
Error
ORA-02270: no matching unique or primary key for this column-list
Reason
Parent table (B) does not have any primary key for matching with child table (A)
Solution
SQL>
alter table B
add constraint ID_PK
primary key (id);
Showing posts with label foreign key. Show all posts
Showing posts with label foreign key. Show all posts
Friday, 23 March 2018
Wednesday, 25 October 2017
ORA-02266 solution
Action
A table has a PK (Primary Key)
B table has a FK (Foreign Key) and referenced to A table's PK
-> User tries to truncate table A
Error
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution I
Disable B table's FK
SQL> alter table HR.B disable constraint FK_B;
-- e.g. schema HR
Solution II
First truncate table B
SQL> truncate table B;
Then truncate table A
SQL> truncate table A;
A table has a PK (Primary Key)
B table has a FK (Foreign Key) and referenced to A table's PK
-> User tries to truncate table A
Error
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Solution I
Disable B table's FK
SQL> alter table HR.B disable constraint FK_B;
-- e.g. schema HR
Solution II
First truncate table B
SQL> truncate table B;
Then truncate table A
SQL> truncate table A;
Labels:
foreign key,
ORA-02266,
oracle,
primary key
Subscribe to:
Posts (Atom)