Red Gate forums :: View topic - How to change primary key column in database and preserve da
Return to www.red-gate.com RSS Feed Available

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

How to change primary key column in database and preserve da

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
jonswaino



Joined: 23 Jul 2008
Posts: 11

PostPosted: Thu Nov 19, 2009 11:08 am    Post subject: How to change primary key column in database and preserve da Reply with quote

Hi,
I'm having trouble after some substantial re-factoring work to our database schema. I've had change the primary key column on about 10 tables. When I run the SQL Compare I get high warnings on 2 of those tables.

The warning is:

Severity: High
Object: CU_G_PRIORITIES
Title: The column [APPLICANTID] on table [dbo].[CU_G_PRIORITIES] must be added but has no default and does not allow NULL values. The table must be rebuilt. If the table contains data then the migration script will not work. To avoid this, add a default to the column or mark it as allowing NULL values.

I don't want to lose any data because its vital we preserve the data. I have other tables which have the primary key column name changed and the warning is only medium:

e.g.:

Object: CU_A_ADDITIONAL_OCCUPANTS
Title: Column [CUSTOMER_ID] on table [dbo].[CU_A_ADDITIONAL_OCCUPANTS] could not be matched to a column in the source table. The data in this column will be lost.

What is the resolution to this? I would have thought the migration script would select the data out of the table, drop the table, create a new one without constraints, re-insert the data and then re-apply constraints.

As long as the column hasn't changed order within the table it should be ok? The PK name was CUSTOMER_ID which has now changed to APPLICANTID
Back to top
View user's profile Send private message
jonswaino



Joined: 23 Jul 2008
Posts: 11

PostPosted: Thu Nov 19, 2009 6:01 pm    Post subject: Reply with quote

Just an update, I've been looking at this all today and still not got anywhere. This is not so much a problem with Primary Keys (as the title suggests). Its more a problem with the foreign key, although I removed the constraint so its just like a regular column to try and investigate what is causing the problem.

I created 2 very simple test databases (TestDB1, TestDB2).
Then in each, I created a very simple table (TestTable).


Step 1
--------
The table is created like so:

CREATE TABLE [dbo].[TestTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE Latin1_General_CI_AS NULL,
[CustomerId] [int] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Step 2.
---------
Now in my 2nd database I create exactly the same table but instead of having a CustomerId column I renamed it manually to ApplicantId, to simulate me renaming a foreign key (again, I removed the constraints for simplicity so its just like any other regular column), but no NULLs are allowed.

Step 3.
---------
Run SQL Compare and attempt to sync from TestDB2 to TestDB1.
I would expect that SQLCompare tries to select data into a temporary table, drop the table, create the new table and select the data back into it. If the column is in the same location, then the select should select into all the new columns in the correct order.

However, SQLCompare gives me the following error:

The column [ApplicantId] on table [dbo].[TestTable] must be added but has no default and does not allow NULL values. The table must be rebuilt. If the table contains data then the migration script will not work. To avoid this, add a default to the column or mark it as allowing NULL values.

So ok, how do I go about changing the foreign key column name without adding a column and allow NULLs?

We have a build system, and everything is checked out from source safe. SQL compare then syncs from a scripts folder to the database. We need SQLCompare to work in one go without fiddling with data in the database, or creating extra migration scrips.

What baffles me is that this error seems to be displayed for any column rename that is peformed whilst it has a NON-NULL policy. Surely this is going to be a big problem.
Back to top
View user's profile Send private message
Simon C



Joined: 26 Feb 2008
Posts: 140
Location: Red Gate Software

PostPosted: Fri Nov 20, 2009 11:46 am    Post subject: Reply with quote

SQL Compare only does a column rename when the old and new column names are reasonably similar to each other, otherwise it does a separate drop & add. Currently, you cannot map columns together like you can schemas (this is a feature to be considered for a future version)

The warning is given because SQL Server throws an error if a NOT NULL column is added to a table with data in it and no default is defined on the table. If you try this yourself, you'll find the following error is displayed:

Code:
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column.


In these cases, the user has to edit the script themselves to either add a default or perform the rebuild themselves.
Back to top
View user's profile Send private message Send e-mail
jonswaino



Joined: 23 Jul 2008
Posts: 11

PostPosted: Tue Dec 01, 2009 11:24 am    Post subject: Reply with quote

After some thought on this, the best way forward for us is to carry out the migration to our db schema scripts stored in source control. Backup our live database, clear out any old data from the database, and then carryout an automatic build of our schema to the live database.

We could then create some manual scripts to then copy the data from the backup database into the new schema. We might be able to do this using SQL Data Compare. If not, we could just restore the database to a different database, and then either copy from that database, or copy the old tables with data into same tables tagged with '_old'.

We could then create manual migration scripts to copy the data from the old tables to the new.

It might be worth investigating the possibility of allowing columns to be mapped if this problem arises, similar to SQL Data Compare. However, I don't know how this would fit into an automated build scenario. A migration folder which stores these mappings?
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1124
Location: Twitter: @dtabase

PostPosted: Mon Oct 17, 2011 11:57 pm    Post subject: Reply with quote

SQL Compare 9.5 not only allows column mapping, but also has a migrations feature, allowing the user to specify their own custom scripts to override default SQL Compare behavior. This works in conjunction with SQL Source Control.

http://www.red-gate.com/MessageBoard/viewtopic.php?p=51312#51312

Let us know if this meets your expectations.

David Atkinson
Red Gate Software
Back to top
View user's profile Send private message Send e-mail
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