Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-11 : 08:49:13
|
| Joel writes "Hello,I'm in charge of reworking a relational database design and I want to get rid of all the blasted GUIDs they used (for no reason, trust me) and move to a incremental INT data type for the keys. I need to keep all of the parent child relationships intact though. How can I do this?Thanks,Joel" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-11 : 09:51:09
|
| It isnt a good idea to have a identity key as a primary key. As you are refining your design you can do away with it and define new primary key.Check this Article by Rob it should help you.http://www.sqlteam.com/item.asp?ItemID=2599--------------------------------------------------------------"Happiness is not something you experience, it's something you remember." |
 |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-02-11 : 10:18:38
|
quote:
It isnt a good idea to have a identity key as a primary key. As you are refining your design you can do away with it and define new primary key.Check this Article by Rob it should help you.http://www.sqlteam.com/item.asp?ItemID=2599
Nazim,As much as I like Rob there were some good reasons why identity columns can be used as pk's in the article's discussion thread. If you haven't read it check it out. To answer Joel's question. Here is how I MIGHT do it (this should get me a few responses  .)1. Create a new table with a duplicate structure by using a "select... into" statement with a where clause that will fail for all rows (where 1 = 2).2. Add an identity field to the new empty table3. Run an "insert... select" query from the main table to the new table. This should populate it and you'll have your identity values.4. Add a int column to the child table5. Update the child table with the identity value from the parent to the fk column of the child by joining on the former pk/fk GUID columns.6. Drop any DRI constraints necessary.7. Drop the GUID columns.8. Drop the original parent table.9. Rename the parent table with the identity column to the that of the old parent table.10. Add any indexes necessary on the parent table11. Recreate any indexes necessary on the child table.my 2c,Justin |
|
|
|
fumble
Starting Member
1 Post |
Posted - 2002-03-04 : 18:27:16
|
| Justin,Thanks so much for your help. I have moved the data over using your method with out any problems. It went great!Thanks again,Joel |
|
|
|
|
|
|
Moving from GUID based relations, to IDENTITY based relations