Need to add 2 PK to existing table

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to add 2 PK to existing table

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-25 : 11:25:46

I have 627836 records in an existing table. I need to add two primary keys how do I do this?

I tried this:

ALTER TABLE T16pendall ADD PRIMARY KEY (cos);
ALTER TABLE T16pendall ADD PRIMARY KEY (mft_posn1_cd);

But got this error message:

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'T16pendall'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-08-25 : 11:36:13

You cannot have more than one primary key constraint on a table, and no columns in the primary key can be nullable.

CODO ERGO SUM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-25 : 12:06:28

If you want a composite PK...

ALTER TABLE T16pendall ADD PRIMARY KEY (cos, mft_posn1_cd);

If you want them both to be unique, can make one the PK and the other unique.

And, as MVJ said, can't be nullable for PK

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-25 : 12:43:04

Thanks so you are saying somewhere I have a null value in one of the two fields?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-25 : 12:54:16

Not necessarily. It means the columns allows nulls. Need to change it not to allow them.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-25 : 13:42:09

Thanks how would I add that to this? Makinig sure no nulls are allowed?

ALTER TABLE T16pendall ADD PRIMARY KEY (cos, mft_posn1_cd);