CHECK constraint SQL problem

Question

I am trying to add a CHECK constraint using the Alter Table SQL command in the QBE window. When I try to run the query, I get an error with the CHECK word highlighted. Am I able to create this query in the QBE window? If so, what is wrong? and secondly, if it can be done, how would I constrain it to two values (ie 'WA', 'OR')?

ALTER TABLE licState

   ADD CONSTRAINT CheckLic

  CHECK (car_State = "WA");

Thank you for your help

Answer 1

I always do these things in SSMS which is so much more powerful. However, your approach should work if you had the syntax right. In this case the state name needs to be wrapped in single-quotes.

Multiple states can be handled with the IN clause:

ALTER TABLE SalesLT.Address

ADD CONSTRAINT CheckLic

CHECK (StateProvince IN ('AZ', 'WA'));

Answer 2

I don't know if things have changed in Access 2010, but I think it always used to be the case that a CHECK CONSTRAINT could not be applied in the query designer in SQL view, but only via code, which can simply be entered in the debug window:

To add constraint:

CurrentProject.Connection.Execute "ALTER TABLE licState ADD CONSTRAINT CheckLic CHECK(car_State IN ('AZ', 'WA'))"

To remove constraint:

CurrentProject.Connection.Execute "ALTER TABLE licState DROP CONSTRAINT CheckLic"

However, why not simply set the car_State column's ValidationRule property in table design view?  You only really need to apply a CHECK CONSTRAINT via DDL for more complex constraints, e.g.

CurrentProject.Connection.Execute "ALTER TABLE Customers ADD CONSTRAINT MaxTotalCredit CHECK((SELECT SUM(CreditLimit) FROM Customers)<=1000000)"