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.

I have a table with the following columns:

ID, STRING_VALUE, ENUM_VALUE, CATEGORY_IDENTIFIER

I'd like to create a condition that won't allow two identical ENUM_VALUEs, but only if their ID is also the same.

For example:

1  BLUE    4  6   --> Good
2  BLUE    4  6   --> Good
3  BLUE    4  6   --> BAD
3  GREEN   4  6   --> BAD

Is there any way to create such a constraint?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Create a unique constraint on the 2 columns.

ALTER TABLE schemaName.tableName ADD CONSTRAINT
        uniTableName UNIQUE NONCLUSTERED
(ID, ENUM_VALUES)
share|improve this answer
    
doesn't that make ID and ENUM_VALUES unique? and if it doesn't, the reason this works is because we treat ID and ENUM_VALUES as one "object"? –  GLaDOS Jan 27 at 20:52
1  
It makes the combination of ID and ENUM_VALUES unique. It's similar to creating a NONCLUSTERED PRIMARY KEY on the table but you can create more than one of them. IE you can only have one PRIMARY KEY but you can have multiple UNIQUE CONSTRAINTS. –  Kenneth Fisher Jan 27 at 20:56

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.