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 need to create a check constraint, the problem i face is that the list of values accepted by the constraint changes in the future (ex:now "red,green and blue" in the next month "red,green,blue and white"). How to do this ?

share|improve this question
3  
Perhaps making it a FOREIGN KEY constraint to a reference table? –  ypercube Mar 2 '13 at 9:50
    
foreign key is not created for this –  user2080105 Mar 2 '13 at 9:51
3  
I agree with ypercube: a foreign key to a "lookup table" is what you should use. –  a_horse_with_no_name Mar 2 '13 at 10:05

1 Answer 1

up vote 9 down vote accepted

You should do this with a foreign key constraint.

You can create a check constraint with the following definition

CREATE TABLE T
(
Color varchar(10) CHECK (Color in ('red','green','blue'))
)

But there is no way of altering a check constraint definition without dropping it and recreating it (thus requiring all rows to be revalidated against the new definition)

To modify a CHECK constraint, you must first delete the existing CHECK constraint and then re-create it with the new definition.

This is trivial to do with a Foreign Key constraint

CREATE TABLE Colors
  (
     Color VARCHAR(10) PRIMARY KEY
  )

INSERT INTO Colors
VALUES      ('red'),
            ('green'),
            ('blue')

CREATE TABLE T
  (
     Color VARCHAR(10) REFERENCES Colors
  ) 

Though I'd probably introduce a surrogate key to the Colors table rather than storing the string repeatedly in the main table.

I have come across the argument before that using a check constraint is somehow "more correct" than using foreign keys and a lookup table but the advantages of the lookup table to me are.

  1. Easier and more efficient to add items to the list.
  2. Easier to get a distinct list of allowable colours (e.g. to display in a listbox in your application)
  3. Using a fixed length integer surrogate key can have performance advantages compared to a variable length string both in terms of reducing row size and avoiding fragmentation on updates.

NB: It is possible to have a check constraint reference a scalar UDF that in turn references a table but this approach should be avoided. It does not simulate a foreign key correctly (e.g. does not validate on DELETE FROM Colors)

share|improve this answer
    
but what i find it strange is to change all my database model only for a constraint –  user2080105 Mar 2 '13 at 11:39
    
@user2080105 - It's not exactly a huge change to the model and gives you greater flexibility and efficiency. You can stick with using a check constraint if you really want but as I say in my answer altering this will in fact require CREATE-ing a new constraint and will require all rows of the table to be read to validate it. Additionally if you are using a FK it is trivial to add an admin interface that allows new colours to be added as it just adds new rows to that table. –  Martin Smith Mar 2 '13 at 11:45
2  
@user2080105 - Then you obviously run the risk of getting invalid data such as "fish" stored instead of an expected colour. –  Martin Smith Mar 2 '13 at 11:51
3  
@user2080105: Why do you think that is strage? It's the standard way to model such a constraint in a relational database. –  a_horse_with_no_name Mar 2 '13 at 12:16
1  
@Catcall - That's true. And in the case that the new constraint is just the old constraint with some additional items in the list it shouldn't cause an integrity problem (as long as the new constraint is created before the old constraint is dropped), It does mean the constraint is untrusted however and can't be used by the optimiser. e.g. For a trusted constraint Color in ('red','green','blue') a query on WHERE Color = 'Purple' wouldn't touch the table at all. –  Martin Smith Mar 2 '13 at 12:59

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.