New Table Column - Foreign Key Constraint

MeshMan

Hi,

Can someone please advise me how to get around the following problem?

The database I'm working with must conform to a column having a default value and no nulls are allowed. So, foreign key columns usually have just "0" as their default value.

When I'm sychronizing a schema, it inserts a table column (a foreign key column) which I assume is then trying to fill the column with the default value "0" which doesn't exist in the foreign table, and therefore throws a foreign key constraint error.

Has anyone experience anything like this before? Are there any flags I can set on the sync methods that helps get round this problem?

I'm thinking I need to manually keep the constraints disabled on the entire database and then make data changes, then switch them all back again manually - but will SQL Compare have a problem where the source database constraints are all enabled and therefore if it sees the target database constraints disabled, it will enabled them again automatically?

Regards,
Martin

Michelle T

SQL Compare will indeed synchronize the 'enabled' status of your constraints. You could tell it to ignore foreign key constraints entirely, which will stop it synchronizing the 'enabled' status but also stop it spotting other differences and synchronizing those.

MeshMan

That's not really going to help me then as when new tables are created or existing ones changed, the foreign keys still need to be synch'd.

I'm thinking that I will just have to make the specific table change myself with a pre-run script, but that goes against the use of the engine. :?

MeshMan

Can I get anymore help with this as I'm still not aware of any way of getting around this without disabling foreign key constraints which would be useless as I need them all comparing/synching etc.

So the rules are, never allow null values on a column, inserting a foreign key column that is referencing a new table that has no data - what's the best way of handling this?

Michelle T

SQL Compare uses the default value specified for a column if you add a column to a table that already has data in it and specify that the new column can't contain nulls.

I'm not sure what values you expect SQL Compare to put into this new column - because it's set to NOT NULL we have to put something there in order to be able to preserve the data already in the table.

Do you need to preserve the data in the target table? If not, you can just truncate the table before synchronization and the problem goes away. You could also move the data to a temporary store of some kind before the synchronization and then move it back manually afterwards.

Your problem seems to be that you want to leave your database in an invalid state after the synchronization (having a NOT NULL foreign key column which doesn't have any valid records to point to, alongside already existing data which means something has to go in that column). Do you then intend to synchronize data across to fill in the foreign key relationship? What is the desired end result of the whole operation?

MeshMan

Hi Michelle,

You've hit the nail on the head there. I imagined leaving the database in an invalid state which then I would populate the relevant tables and then re-enable all the foreign key constraints now that the values would be valid in the new column.

However, I don't think the synch library allows creating the foreign key constraints but leaving them disabled?

I loved your idea about having a temporary table for moving the data into, then truncating the changing table. The only problem with this is that any depending tables would also need their data moving or constraints disabling. That could get really messy?

How about a 2 pass approach where I ask the synch to ignore keys, then populate my tables with data, then run a another synch to then include the keys. What worries me about that though is that the compare already takes about 20 minutes to perform, I would hate to think would get longer by another substantial amount of time.

The database in question has:

462 tables.
483 views.
4,682 procedures.
3,946 constraints.

Any more thoughts would be appreciated.

Regards,
Martin

Michelle T

If you can change the source database and it's not being actively used while you're doing the comparison / synchronization, you could disable the foreign keys on the source database as well, do the synchronization, re-enable the keys on the source, populate the new tables, then re-enable the keys on the target.

Otherwise your two-pass approach, ignoring the keys to start with and then synchronizing them onto the populated tables, sounds like the best way to proceed.

If you have long dependency chains amongst your tables then moving out the data without disabling all the keys will probably get unreasonably messy, yes.

(Twenty minutes is quite a long time - I'm assuming the performance bottleneck is getting the contents of those 4,682 stored procedures over the network or something?)

MeshMan

I don't think it's network latency as the comparison is done purely on local across 2 DB instances when I'm testing.

Your SQL Compare tool can compare any two databases in about 30 seconds, yet a tiny wrapper over the SDK takes 15-20 mins easy. Perhaps it's something we're not doing right, yet there isn't much code. I see it simply building a differences array, selecting them all, and then executing the work through a BlockExecutor...

Sorry to digress. So, I'll give the two pass approach a try but meanwhile, need to look at sorting out the time its taking.

Thanks,
Martin.

Michelle T

If you could send me the code you're using (to michelle.taylor@red-gate.com) I can take a look at it and see if you're doing anything that might obviously cause that speed problem.