Changing a Primary Key to Foreign Key

I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,
> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.

> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,
|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:

>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)