CODEWITHSUNDEEP
sqloracle-databasenullforeign-keysconstraints
Andreas Andreou
Andreas Andreou

Reputation: 848

Oracle SQL Foreign Key constraint that ignores null or historic values

I am trying to add a constrain to a database table that I want to modify. I want to add a constraint on a column so it references a primary key of another tables. Easy enough, I just have to add a foreign key constraint. The problem is that the column already has some values that are null or something that is not part of the table I will be referencing.

My question is how do I add a constraint that references a primary key but can also accept null values (the primary key always has a value) and how to ignore the existing values so far. Is it possible? If the second part is not, I am thinking I could always write a script that updates all the nonsense values (they have a format of sort if that I can reg ex) to null so they only thing I have to figure out is how to add a foreign key constraind that also accepts null values

Upvotes: 1

Views: 7115

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

Firstly, there's nothing stopping you from adding a referential constraint on a column that has NULLs - foreign key constraints are only enforced for non-NULL values.

Secondly, if there are existing values that do not exist in the parent table, and you can't fix them, you do have the option in Oracle to make the constraint only validated for newly inserted or updated rows, using the NOVALIDATE option, e.g.

ALTER TABLE x ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent (id) NOVALIDATE;

The only downside to using the NOVALIDATE option is that the query optimizer will not rely on the FK constraint, and will execute queries with the assumption that there may be rows that do not have a matching parent row.

It would be a good idea if you are able to fix the missing values, afterwards to alter the constraint to VALIDATE.

Upvotes: 5

user4261009
user4261009

Reputation:

Primary keys can not contain NULL values in all databases (proof)

To add a foreign key you should do something like:

ALTER TABLE table1
ADD CONSTRAINT fk_table1_2
FOREIGN KEY (column1)
REFERENCES table2(column2);

Upvotes: 0

Related Questions