How to alter primary key constraint for 2 columns in same table in SQL Server?

Question

In SQL Server, I already created a table called tblDatabase with some columns. I want to add a primary key on the two columns DatabaseName and Servername.

I tried to add:

ALTER TABLE tblDatabase ADD CONSTRAINT pk_DatabaseServer 
                            PRIMARY KEY (DatabaseName, ServerName) 

in check constraints but I get pop up error that say

error validating constraint 'ck_tblDatabase'.

Did I miss something?

I also have ServerName as PK in tblServer that already relationship with tblDatabase. Is that the reason why?

Answer 1

Don't enter this into the check constraint dialogue box. That only allows you to enter expressions for check constraints.

To create your primary key constraint you should just execute your TSQL ALTER TABLE script in a new query window.

To create this using the visual tools you would need to use the indexes/keys dialog or in the table designer view select the two columns and choose "Set Primary Key" from the shortcut menu.

I would avoid this last approach for a composite PK however as AFAIK it gives you no way of controlling the order that the index keys will be defined in.