Tuesday, 29 January 2013

Primary Key and Foreign Key on Char field (ORA-02291: integrity constraint)



I have tables a and b with character field as its primary key foreign key relation. The primary key field is with char(5) and foreign key field is with char(50). When I tried to insert to child table selecting from primary key column i get "ORA-02291".

After giving little rest to brain I figured out whats going wrong. When insert is fired to column with char data type of child table, oracle is appending spaces on top of the value coming from primary key column (because its a char field!!!) . So primary key column value + spaces to fill the character field that makes the child a different value, that's the reason for ORA-02291.

This is how it works

SQL> create table a(id number,rel char(5)) ;

Table created.

SQL> create table b(id number,rel char(5)) ;

Table created.

SQL> create table c(id number,rel char(50)) ;

Table created.

SQL> alter table a add(constraint a_pk primary key (rel));

Table altered.

SQL> alter table b add(constraint b_fk foreign key (rel) references a(rel));

Table altered.

SQL> alter table c add(constraint c_fk foreign key (rel) references a(rel));

Table altered.

SQL>
SQL> insert into a values(1,'A') ;

1 row created.

SQL> INSERT INTO B SELECT * FROM A;

1 row created.

SQL>INSERT INTO C SELECT * FROM A;
INSERT INTO C SELECT * FROM A
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.C_FK) violated - parent key not found


SQL> alter table C modify(rel char(5)) ;

Table altered.

SQL>INSERT INTO c SELECT * FROM A ;

1 row created.





The varchar version



SQL> create table a(id number,rel varchar2(5)) ;

Table created.

SQL> create table b(id number,rel varchar2(5)) ;

Table created.

SQL> create table c(id number,rel varchar2(50)) ;

Table created.

SQL> alter table a add(constraint a_pk primary key (rel));

Table altered.

SQL> alter table b add(constraint b_fk foreign key (rel) references a(rel));

Table altered.

SQL> alter table c add(constraint c_fk foreign key (rel) references a(rel));

Table altered.

SQL> insert into a values(1,'A') ;

1 row created.

No comments:

Post a Comment