Skip to Main Content
  • Questions
  • Adding constraints to an integer attribute that defaults to 0

Breadcrumb

Question and Answer

Connor McDonald

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library