constraint issue

[clinton]

I have a table(emp) that has 4 check constraints on a column(first_name).

alter table emp add constraint ck_first_name check (first_name in ('JOHN','DAN','ROB','NEAL'));

The table has been loaded with a lot of data. Now I want to add the name DAVID to the first_name column as one of the check constraints.

My question is how do I do that? Do I just drop the entire constraint and recreate it or because the table is loaded with data, it cannot be done.

What is the best way to do this?

Thanks,
clinton

[pipo]

well, I do not see the suefulness of such a constraint ... you need to alter it, but I don't understand why it is made that way : if your table contains all the employees, then there is no need to check their name, and if it does contain several lines for an employee, then you should have a table with the enmplyees names only, and a foreign key between emp and this table ...

[clinton]

That is just an example. I just want to know how the it's done

[pipo]

as a general thing :
you have a reference table (for example a table emp with all employees), and in your other tables where you use the employee's name or ID, you just add a foreign key which references emp, that way :
1) you are sure that one cannot add a line with an employee that does not exist in emp
2) you cannot delete a line in emp if the employee is still referenced elsewhere

so it's very rare to have check constraints like the one you proposed

[clinton]

Assuming the table has no parent-child relationship, can I modify the check constraint?