Foreign key constraint is created before it is valid
NightOwl888
Posts: 6
I just downloaded the trail version of SQL Packager and I am working on creating my first package. I am trying to deploy a database from one server to another.
However, I have a table that I recently created that is the PK in the relationship with another table that already exists (the FK). The problem I am having is that the table schema code tries to create the foreign key, but the foreign key won't be valid until the data is put into the PK table. This makes the foreign key creation code fail and it rolls back the entire transaction.
Is there any way to make the packager generate the code I need to first enter the data and then apply the foreign key afterward?
However, I have a table that I recently created that is the PK in the relationship with another table that already exists (the FK). The problem I am having is that the table schema code tries to create the foreign key, but the foreign key won't be valid until the data is put into the PK table. This makes the foreign key creation code fail and it rolls back the entire transaction.
Is there any way to make the packager generate the code I need to first enter the data and then apply the foreign key afterward?
-NightOwl888
Comments
Have you tried using the disable foreign keys and disable primary keys options in the data settings? This will temporarily drop the foreign keys so that violations to the constraints will be allowed temporarily. Because data updates by Packager happen in batches by table, FK violations are typical at some stage during the synchronization, but sort themselves out provided you do synchronize both tables in the FK relationship.
I hope this helps!
I ended up saving the scripts and manually ensuring the constraints weren't created until after the data was entered into the table. SQL Packager still saved me more than 99% of the work - I only had to change about 5 lines of code.
After analyzing what was happening, I think I found the cause. This database was upgraded from SQL Server 2000. I don't know if it is still the case, but there were previously 2 copies of the schema kept - the physical schema and the code that was used to create it. In my case, the table had been renamed (along with the physical constraint), but the code script for the constriant had a different name.
Anyway, now that it is fixed, I doubt I will have issues in the future.
In a new database, whether or not the foreign key checks the existing data (WITH CHECK) should be irrelevant because there is not yet any data to check, so the ecript should not fail during the schema creation phase -- the data option to disable the foreign keys should then kick in and disable the dey during the synchronization and turn it back on at the end without checking the data. However, a database upgrade package is a different animal. If the existing data is inconsistent and you apply a foreign key by altering the inconsistent table, then this is something that may need a manual workaround.
If you have got a solid solution to the problem in place, though, I'm glad about that! If you need anything else , please don't hesitate to contact us.