Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Nov 18 '12 at 1:45

This question came from our site for professional and enthusiast programmers.

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
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thank! I got this work. –  StudentIT Nov 14 '12 at 15:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.