Primary Key Question

If I try to run a script to create a primary key:

ALTER TABLE [dbo].[Drawers]

ADD PRIMARY KEY (ID)

Why does SQL Server name the primary key in this format below? The last section of letters and number changes each time I drop and create a key:

PK__Drawers__1C873BEC

I need a unique name so that I can create one script that will drop and create indexes anywhere. I cannot do this with SQL Server creating a different key name everytime a key is created. How can I resolve this?

Thanks,

cj

If you are wanting to have a fixed primery key, the best thing you can do is name it yourself:

ALTER TABLE [dbo].[Drawers]

ADD CONSTRAINT PK_DRAWERS
PRIMERY KEY (ID)

|||

What if the primary key column, ID does not exist to begin with?

Say I add the column for the primary key as:

ALTER TABLE dbo.TransactionLineItems

ADD ID INTEGER

Then, the ID column will be null and I will not be able to add a primary key to it if it is already null:

ALTER TABLE dbo.TransactionLineItems

ADD ID INTEGER CONSTRAINT PK_TransLineItems_ID IDENTITY PRIMARY KEY

I get the error:

Column names in each table must be unique. Column name 'ID' in table 'dbo.TransactionLineItems' is specified more than once.

|||

So if the column that you wish to use as a Primary Key does not exist, AND if you wish to use an IDENTITY column for that Primary Key, AND you want to definitively name the Primary Key, try this two (2) step process.

Code Snippet

ALTER TABLE dbo.TransactionLineItems

ADD [ID] int IDENTITY

GO

ALTER TABLE [dbo].[Drawers]

ADD CONSTRAINT PK_TransactionLineItems PRIMARY KEY ([ID])
GO

|||

To me if you get the message that you say it means that you are mistaken and in fact that column -- ID -- already DOES exist. Can somebody please check me on this?

|||

Kent,

My understanding is that the error occurred after he first added an [ID] column, and then attempted to add another [ID] column as a PK constraint.

|||Yes, this is the syntax I needed. Thanks!