Replacing a Primary Key

Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
ThanksCan you give us some more detail? What exactly is being moved? How about
posting the DDL for the tables involved?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8a399b37132@.uwe...
Is there rules to replaceing a primary key? What are they?
I have a primary key that is a foreign key in another table.
I created a temp table and moved the into the temp table.
Becuase of the foreign key relationship I can't Drop the table.
Any help will be appreciated
Thanks|||We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Could you change the FK to use ON DELETE CASCADE and then just update the
PK?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8b87d03ca52@.uwe...
We have a ticket entry system that uses Social Security numbers as the
Primary key Well we all know with privacy act and everything we need to
replace this with a synthetic key.
We have a Tech Table which has the primary key we want to change but we want
to keep the primary key information.
The foreign key is in table Ticket which is how we know who did the
troubleshooting.
Tom Moreau wrote:
>Can you give us some more detail? What exactly is being moved? How about
>posting the DDL for the tables involved?
>Is there rules to replaceing a primary key? What are they?
>I have a primary key that is a foreign key in another table.
>I created a temp table and moved the into the temp table.
>Becuase of the foreign key relationship I can't Drop the table.
>Any help will be appreciated
>Thanks|||Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||I'd do the following:
1) drop the FK constraint
2) add the column for the new key in both tables
3) update the data in the child table - sort of like the following (you
didn't give us your DDL):
update Child
set
NewCol = (select (p.NewCol) from Parent p
where p.OldCol = Child.OldCol)
4) drop the OldCol column from the child table
5) add the FK constraint on the new columns
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe" <u19885@.uwe> wrote in message news:5d8c88defc668@.uwe...
Tom What i want to end up with in the Tech table is:
- new synthetic key (id)
- Social Security Number (Current Primary Key)
- rest of table
With out loosing any of the current data
Thanks so so much
Tom Moreau wrote:
>Could you change the FK to use ON DELETE CASCADE and then just update the
>PK?
>We have a ticket entry system that uses Social Security numbers as the
>Primary key Well we all know with privacy act and everything we need to
>replace this with a synthetic key.
>We have a Tech Table which has the primary key we want to change but we
>want
>to keep the primary key information.
>The foreign key is in table Ticket which is how we know who did the
>troubleshooting.
>Tom Moreau wrote:
>>Can you give us some more detail? What exactly is being moved? How about
>>posting the DDL for the tables involved?
>[quoted text clipped - 10 lines]
>>Thanks|||Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||I'd keep the Tech table.
1) Add the new tech ID column to Tech. Populate it as required.
2) Drop the FK constraint on Ticket:
alter table Ticket
drop constraint [The FK constraint name]
3) Update the Ticket table's Tech_Id column:
update Ticket
set
NewCol = (select (p.NewCol) from Tech p
where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
is the new column name in the Tech table
4) Add the FK back:
alter table Ticket
add constraint [The FK constraint name]
references Tech (Tech_id)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8e2bbeb8a32@.uwe...
Is this the Steps Tom:
.
_______
| Tech | -- 1 -- --N [ Ticket ]
--
Social (Primary Key) ID
Name Tech_id references
Social (FK)
Address
zip
Phone
I need to add the new Primary to Tech. and still keep Social but not as
primary key
I believe these are the steps to accomplish whjat i need to do See above for
diagram
1. I think i need to create tech_temp and and copy my date to this table
2. Drop the foreigh key on Tech_id
3. Drop table tech
4. Create new Table Tech with ID sysenthic key
5. copy the data over from temp table
6. drop the temp table
7. recreate the Foreign key constraint on Ticket
Just don't know the commands to accomplish.
o drop the Foreign Key Contraint from Table Ticket Column Tech
I would
Tom Moreau wrote:
>I'd do the following:
>1) drop the FK constraint
>2) add the column for the new key in both tables
>3) update the data in the child table - sort of like the following (you
>didn't give us your DDL):
>update Child
>set
> NewCol = (select (p.NewCol) from Parent p
> where p.OldCol = Child.OldCol)
>4) drop the OldCol column from the child table
>5) add the FK constraint on the new columns
>Tom What i want to end up with in the Tech table is:
> - new synthetic key (id)
> - Social Security Number (Current Primary Key)
> - rest of table
>With out loosing any of the current data
>Thanks so so much
>Tom Moreau wrote:
>>Could you change the FK to use ON DELETE CASCADE and then just update the
>>PK?
>[quoted text clipped - 15 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When do i make the new Column the Primary key and whats the code to do that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||When you add the new Tech_Id column to the Tech table, then:
alter table Tech
add
constraint PK_Tech primary key (Tech_Id)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"traderjoe via SQLMonster.com" <u19885@.uwe> wrote in message
news:5d8fbc24e3545@.uwe...
When do i make the new Column the Primary key and whats the code to do
that?
Sorry, I am having a hard time with this, sometimes I am an idiot..
Tom Moreau wrote:
>I'd keep the Tech table.
>1) Add the new tech ID column to Tech. Populate it as required.
>2) Drop the FK constraint on Ticket:
>alter table Ticket
>drop constraint [The FK constraint name]
>3) Update the Ticket table's Tech_Id column:
>update Ticket
>set
> NewCol = (select (p.NewCol) from Tech p
> where p.Tech_id= Ticket.Tech_id) -- assumes
> Tech_Id
>is the new column name in the Tech table
>4) Add the FK back:
>alter table Ticket
>add constraint [The FK constraint name]
>references Tech (Tech_id)
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinpub.com
>.
>Is this the Steps Tom:
>.
>_______
>| Tech | -- 1 -- --N [ Ticket ]
>--
>Social (Primary Key) ID
>Name Tech_id
>references
>Social (FK)
>Address
>zip
>Phone
>I need to add the new Primary to Tech. and still keep Social but not as
>primary key
>I believe these are the steps to accomplish whjat i need to do See above
>for
>diagram
>1. I think i need to create tech_temp and and copy my date to this table
>2. Drop the foreigh key on Tech_id
>3. Drop table tech
>4. Create new Table Tech with ID sysenthic key
>5. copy the data over from temp table
>6. drop the temp table
>7. recreate the Foreign key constraint on Ticket
>Just don't know the commands to accomplish.
>o drop the Foreign Key Contraint from Table Ticket Column Tech
>I would
>Tom Moreau wrote:
>>I'd do the following:
>[quoted text clipped - 25 lines]
>>Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1