February 2, 2009 at 8:24 am
Hi all,
I have an old database and I have to create e.g. a one-to-many relation between two existing tables with a lot of data.
The tables are currently linked together with a Primary key in table A of type char(10) and with a foreign key in table B of type char(10). This column is the product number of the product but I want to change the relation to using unique identifiers of type int where the primary key is an autonumber.
1. What is the recommended way to do this?
2. Is my proposed setup better than is currently in the database?
thanks in advance for the help!
February 2, 2009 at 9:29 am
Unless you're having troubles with the system, I wouldn't recommend changing the PK's on the tables.
That said, int is likely to index a bit better than char(10), so you'll get some performance improvements. You need to be sure to keep a unique constraint on the alternate pk as well as on the new identity column because you don't want to allow multiple rows from the business. You should look into using something like the OUTPUT clause to migrate the data into the new structure. As a matter of fact, to ensure integrity and to be a good and thoroughly paranoid DBA, I'd suggest you rename the existing tables, create the new ones with the old name, migrate the data, once successful, remove the old tables, but until successful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2009 at 9:16 am
Hi
Dont touch the existing tables. Create new tables with Int (PK Column) and copy over the data from old tables new ones and do the due diligence to make sure you dont have any issues with the then rename the tables.
Thanks -- Vijaya Kadiyala
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply