Skip to content
Advertisement

Convert foreign key constraint from SQL Server to Oracle

I want to convert the following script(SQL SERVER) to Oracle:

I have tried to run the same script in Oracle:

and I got this error:

I believe that the “WITH CHECK ADD” is the problem!

Advertisement

Answer

See if this helps.

A master table – its primary key column will be referenced from the tdistribucion table.

A detailtdistribucion – table (with only one column, just to demonstrate the issue):

A foreign key constraint, using proper syntax. It will succeed if no records violate the constraint and fail if opposite. Oracle does it at once, you don’t have to run another “check”. As both tables are empty, it’ll succeed:

However, if there were some rows that violate the constraint, you wouldn’t be able to create it:


But, if you create it as enable novalidate, it’ll “discard” rows that violate the constraint, but will check any subsequent inserts:

Just to show that rows that violate the constraint really exist:

This is OK:

This isn’t OK:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement