Using Constraint statement in SQL

[shani20]

I am using MS Access to do a project and I was trying to add CONSTRAINT to check the values being inserted but I kept getting syntax errors and I have looked everything to solve this but was not able to.



ALTER TABLE Parts
ADD CONSTRAINT chk_Category CHECK (Category IN (‘CPUs/Processors’, ‘Motherboards’, ‘Memory’, ‘Hard Drives’, ‘Video Cards/Video Devices’, ‘Power Supplies’, ‘Computer Cases’, ‘Fans/PC Cooling’))

What did I wrong? I am trying to use SQL view on MS Access 2013.

THanks

Shani

[June7]

What you appear to be trying to do is enforce data validation for specific literal strings at table level. I don't think can set up this way, never seen. This is what lookup fields are for (I never build in table) and restricted comboboxes on form.

Do these help
http://www.w3schools.com/sql/sql_constraints.asp
https://msdn.microsoft.com/en-us/lib...ffice.15).aspx

[shani20]

Thanks - I just discovered that MS Access don't accept CONSTRAINT anyway, but the format that I had was correct one. So I will just use the format for homework and add the drop list on my own on MS Access.

Shani

[orange]

Shani,

I have Acc 2010 and tried something I found online. Apparently this was available in 2007 according to the post I saw.

You can add a CHECK constraint in Access, but you must do it directly in code. You can not use the query designer.

I took a sample table in my database and added the Check constraint with this code entered directly in the immediate window.

CurrentProject.Connection.Execute "ALTER TABLE CityTbl ADD CONSTRAINT CheckSt3 CHECK(State IN ('Texas', 'Michigan'))"

I tried adding a record for Oswego , New York and got the attached message.

So the constraint was added and works as expected.

Good luck with your project.