CODEWITHSUNDEEP
oracle-databasekeyconstraints
Mr Magoo
Mr Magoo

Reputation: 37

Oracle foreign key constraints - check constraint syntax?

I have a child table in oracle that has two foreign key columns, relating to two different parent tables. I want to create a constraint that says the child must have at least one of those parents - e.g. 

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1)
   REFERENCES parent_table (column1)
   OR
   FOREIGN KEY (column2)
   REFERENCES parent_table_2 (column1)

This won't work with a foreign key constraint because that can only relate to one parent table - is it possible to do this with a check constraint instead?

Upvotes: 1

Views: 813

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

You can do it with constraints:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name_1
   FOREIGN KEY (column1)
   REFERENCES parent_table (column1);

ALTER TABLE table_name
ADD CONSTRAINT constraint_name_2
   FOREIGN KEY (column2)
   REFERENCES parent_table_2 (column1);

ALTER TABLE table_name
ADD CONSTRAINT constraint_name_3
        check (COALESCE(column1, column2) IS NOT NULL);

Of course for column1 and column2 column you must permit NULL values.

Upvotes: 0

Dmitry Grekov
Dmitry Grekov

Reputation: 688

Foreign key constraints ensure the referential integrity, not mandatory values. I think you have to have to separate FK contraints and additional check constraint like this:

alter table table_name 
    add constraint c_check_cols 
        check(column1 is not null or column2 is not null);

Upvotes: 0

Related Questions