Creating a check constraint with SQL (1 Viewer)

[kcarpy]

Hi. I'm currently frustrating myself to death trying to figure out why this isn't working. I want to create a table using SQL (or VB if necessary) that has validation code, and I've been told I should use check constraints to do that. However, I can't seem to get check constraints to work at all. I'm using Access 2003. Here is an example of what I am trying:

CREATE TABLE test (
  id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
  enterer text(50),
  datent datetime,
  gender integer,
  age integer CHECK (age<90));

No matter what I do, it always gets caught on the CHECK. I've gone online and seen examples, and they all tend to look like this or have constraints added after the variables and separated by commas. That also won't work for me (it thinks "CHECK" is a variable name). I can't figure out what it is that I'm doing wrong. Any help would be greatly appreciated!

 

[boblarson]

Are you creating this table in Access or in SQL Server?

 

[kcarpy]

I'm using Access.

 

[boblarson]

I'm using Access.

Then you can't use that syntax. Access does not use Check Constraint. I may be wrong here but I think you can create the table okay using the SQL command but you'll need to use DAO and the table defs to add the validation rule.

 

[kcarpy]

Ah, I was working with faulty info then. Thanks!

 

[boblarson]

Ah, I was working with faulty info then. Thanks!

Well, I don't know about that, but I've never seen that being used. It may be possible, and if someone says it can be done with Access I would like to see where it says that and give it a good shake down.

 

[LPurvis]

The OLEDB provider for Jet supports the CHECK CONSTRAINT syntax.
http://support.microsoft.com/kb/201888
Note the execution method (ADO only - so as to use that provider).

Bear in mind also that any implementation of it will not appear in a table's design view in Access. (It's not the same as the Validation Rule - but will be reported as such when the data doesn't obey the check conditions).

Cheers.

 

[boblarson]

Interesting.

 

[LPurvis]

Interesting indeed. (Well, to the type of cool people who find such things noteworthy. Not sure everyone would agree with my assessment of what constitutes "cool" though

I neglected to mention that, due to the lack of access of providers other than OLEDB, the table won't be able to be deleted by other means - or have a field referenced in the constraint altered.

This is an alternative rudimentary protection of the table if you're so inclined.
(Of course it doesn't prevent deletion of the table's rows!)

Cheers.

 

[khawar]

I have posted a sample in a thread some time ago it has constraints on table based on other tables and other fields in same tables have a look at it Here

 

[khawar]

And this can be done with a single line of Code using ado to any already existing table

To add constraint use

CurrentProject.Connection.Execute "ALTER TABLE Table1 ADD CONSTRAINT MyConstraint1 CHECK (MyField between 6 and 10)"


To drop constraint use

CurrentProject.Connection.Execute "ALTER TABLE Table1 Drop CONSTRAINT MyConstraint1"


So in your case you can use

CurrentProject.Connection.Execute "ALTER TABLE test ADD CONSTRAINT AgeConstraint CHECK (age < 90)"