Thanks for the question, Fiona.
Asked: March 24, 2017 - 10:49 pm UTC
Last updated: March 26, 2017 - 8:32 am UTC
Version: 1.1
Viewed 1000+ times
You Asked
Hi Tom,
I have two tables, Table A that contains my primary key x and Table B with attribute y that references Table B but I had not added constraints until now.
Thus,
A -> x
B ->y
and y references x.
I need to add a constraint on Table B now with y referencing x
ALTER TABLE `B` ADD CONSTRAINT fk_B_A FOREIGN KEY (`y`) REFERENCES A(`x`);
I am unable to add this constraint because Table A has some records where the y column holds a value 0 as it has a default constraint as NULL but its of integer type. Since this value 0 is not present in column x, I am not allowed to apply the constraint.
How can i find a work around this because I need my records in Table B which have y values as 0, it would work well if it saved it as NULL, I manually updated the records to set y = NULL and the constraints were added.
But for my future records, if I create a record expecting default values updated for y in Table B (which would save it a 0), it would check it up against x from Table A and not find it there and not allow us to create the record.
Is this correct?
I would be super grateful of you could let me know how we can fix this issue.
Thanks a lot for your time,
Fiona
and Connor said...
A couple of options here you could look at
1) add a trigger to transpose zero to null
2) add a virtual column so that your zero's are maintained but the integrity constraint can still be enforced as best as possible
SQL> create table a ( x int primary key );
Table created.
SQL> create table b ( z int, y int default 0 references a(x));
Table created.
SQL>
SQL> insert into a values (1);
1 row created.
SQL> insert into b ( z ) values ( 123 );
insert into b ( z ) values ( 123 )
*
ERROR at line 1:
ORA-02291: integrity constraint (MCDONAC.SYS_C0013120) violated - parent key not found
SQL>
SQL> --
SQL> -- Option 1
SQL> --
SQL> create or replace
2 trigger mangle_b
3 before insert or update on b
4 for each row
5 begin
6 :new.y := case when :new.y != 0 then :new.y end;
7 end;
8 /
Trigger created.
SQL>
SQL> insert into b ( z ) values ( 123 );
1 row created.
SQL> select * from b;
Z Y
---------- ----------
123
1 row selected.
SQL>
SQL> --
SQL> -- Option 2
SQL> --
SQL>
SQL> drop table b purge;
Table dropped.
SQL> create table b ( z int, y int default 0);
Table created.
SQL>
SQL> alter table b add custom_y int generated always as ( case when y != 0 then y end );
Table altered.
SQL>
SQL> alter table b add constraint custom_y_fk
2 foreign key ( custom_y) references a(x);
Table altered.
SQL>
SQL> insert into b ( z ) values ( 123 );
1 row created.
SQL> select * from b;
Z Y CUSTOM_Y
---------- ---------- ----------
123 0
1 row selected.
SQL>
SQL> insert into b ( z,y ) values (1,1 );
1 row created.
SQL> select * from b;
Z Y CUSTOM_Y
---------- ---------- ----------
123 0
1 1 1
2 rows selected.
SQL>
SQL> insert into b ( z,y ) values (1,2 );
insert into b ( z,y ) values (1,2 )
*
ERROR at line 1:
ORA-02291: integrity constraint (MCDONAC.CUSTOM_Y_FK) violated - parent key not found
SQL>
Is this answer out of date? If it is, please let us know via a Comment