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?

  • 3
    Does the table already have a primary key defined? Do you have data that is duplicated across these two rows (if you do - your data prevents the primary key from being created - fix it). – Oded Nov 14 '12 at 15:08

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy