Red Gate forums :: View topic - Foreign key constraint is created before it is valid
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager Previous Versions
SQL Packager Previous Versions forum

Foreign key constraint is created before it is valid

Search in SQL Packager Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
NightOwl888



Joined: 13 Jun 2008
Posts: 4
Location: Los Angeles, CA

PostPosted: Fri Jun 13, 2008 7:32 pm    Post subject: Foreign key constraint is created before it is valid Reply with quote

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?

_________________
-NightOwl888
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Mon Jun 23, 2008 6:19 pm    Post subject: Reply with quote

Hi,

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!
Back to top
View user's profile Send private message
NightOwl888



Joined: 13 Jun 2008
Posts: 4
Location: Los Angeles, CA

PostPosted: Mon Jun 23, 2008 10:24 pm    Post subject: Reply with quote

Yes, I tried that and it still crashed.

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.
_________________
-NightOwl888
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Tue Jun 24, 2008 9:54 am    Post subject: Reply with quote

Hi,

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group