Check Constraints in Access

Check Constraints in Access

The Access Database Engine does support CHECK constraints, but the DDL to create them cannot be executed from the Query Designer in Access. They must be created using VBA code and an ADO connection, like so:

Option Compare Database
Option Explicit

Public Sub AddCheckConstraint()
    Dim strSql As String
    strSql = _
            "ALTER TABLE LEVERANCIER" & vbNewLine & _
            vbTab & "DROP CONSTRAINT chk_postcode;"
    On Error Resume Next
    CurrentProject.Connection.Execute strSql
    On Error GoTo 0
    strSql = _
            "ALTER TABLE LEVERANCIER" & vbNewLine & _
            vbTab & "ADD CONSTRAINT chk_postcode" & vbNewLine & _
            vbTab & "CHECK (" & vbNewLine & _
            vbTab & vbTab & "NOT EXISTS (" & vbNewLine & _
            vbTab & vbTab & vbTab & "SELECT levnr, postcode " & vbNewLine & _
            vbTab & vbTab & vbTab & "FROM LEVERANCIER " & vbNewLine & _
            vbTab & vbTab & vbTab & "WHERE Left(postcode, 4) = '5050' OR Woonplaats = 'Amsterdam' " & vbNewLine & _
            vbTab & vbTab & ")" & vbNewLine & _
            vbTab & ");"
    CurrentProject.Connection.Execute strSql
End Sub

MS-ACCESS: How do you tell if constraints are in place?

CurrentDb.Execute "ALTER TABLE main ADD CONSTRAINT pk_main PRIMARY KEY (x,y,z)"

That statement executed without error. If I was still unsure whether it "worked", I could test by adding a row with values which duplicated the x, y, and z values stored in another row. If the primary key constraint is effective, the duplicate values would trigger an error.

But it would be quicker to open the table in Design View and examine its indexes. DoCmd.OpenTable "main", acViewDesign ...

Alternatively, since the primary key constraint is implemented as an index, and the constraint name is the index name, you could inspect the table's index with DAO.

? CurrentDb.TableDefs("main").Indexes.Count
 1 
? CurrentDb.TableDefs("main").Indexes(0).Name
pk_main
? CurrentDb.TableDefs("main").Indexes(0).Primary
True
? CurrentDb.TableDefs("main").Indexes(0).Fields.Count
 3 
? CurrentDb.TableDefs("main").Indexes(0).Fields(0).Name
x
? CurrentDb.TableDefs("main").Indexes(0).Fields(1).Name
y
? CurrentDb.TableDefs("main").Indexes(0).Fields(2).Name
z

If you want yet another way to check whether the primary key constraint was created, consider the ADO OpenSchema Method. If you're using Dot.Net with OleDb, it provides a similar method.

Constant errors with check constraint in MS Access using SQL

You need to run against a connection and Comp is a reserved word.

ss = "ALTER TABLE [Comp] ADD CONSTRAINT CheckSpeed CHECK (ProcessorSpeed > 0)"
CurrentProject.Connection.Execute ss

Info: Constraints

Reserved words in Jet/Access pre 2007 and ACE/Access 2007-

Create a check constraint in access and/or DAO

Here are some notes.

You can create a Pass-Through query for Oracle (Select menu "Query" > "SQL Specific" > "Pass-Through")

Since Access 2003, you can select SQL Server Compatible Syntax (ANSI 92) (http://office.microsoft.com/en-us/access/HA010345621033.aspx)

A validation rule with VBA / DAO

''Reference: Microsoft DAO x.x Object Library

Dim tdf As TableDef
Dim db As Database

Set db = CurrentDb

Set tdf = db.TableDefs("Table1")

tdf.Fields("aDouble").ValidationRule = "<10"
tdf.Fields("aDouble").ValidationText = "Must be less than 10"

Constraints with ADO / VBA. See [Intermediate Microsoft Jet SQL for Access 2000](http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx)

''Reference: Microsoft ADO Ext. x.x for DDL and Security

Dim cn As ADODB.Connection 'For action queries
Dim rs As ADODB.Recordset  'For select queries
Dim s As String
Dim RecordsAffected As Long

Set cn = CurrentProject.Connection

''You can store sql in a table
s = DLookup("SQLText", "sysSQL", "ObjectName='q1'")
''Result: CREATE TABLE tblCreditLimit (LIMIT DOUBLE)
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''You can run queries from VBA
s = "INSERT INTO tblCreditLimit VALUES (100)"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "CREATE TABLE tblCustomers (CustomerID COUNTER, CustomerName Text(50))"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "INSERT INTO tblCustomers VALUES (1, 'ABC Co')"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "ALTER TABLE tblCustomers " _
   & "ADD COLUMN CustomerLimit DOUBLE"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''You can add contraints using ADO like so:
s = "ALTER TABLE tblCustomers " _
   & "ADD CONSTRAINT LimitRule " _
   & "CHECK (CustomerLimit <= (SELECT LIMIT " _
   & "FROM tblCreditLimit))"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "UPDATE tblCustomers " _
   & "SET CustomerLimit = 200 " _
   & "WHERE CustomerID = 1"
''Error occurs here
cn.Execute s, RecordsAffected

s = "UPDATE tblCustomers " _
   & "SET CustomerLimit = 90 " _
   & "WHERE CustomerID = 1"
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

''Clean up
''You cannot do this through the database window,
''because of the constraint.
s = "ALTER TABLE tblCustomers DROP CONSTRAINT LimitRule "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "DROP TABLE tblCustomers "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

s = "DROP TABLE tblCreditLimit "
cn.Execute s, RecordsAffected
Debug.Print RecordsAffected

Error while using CHECK clause in SQL (MS access)

For Access, you have to introduce your PK and CHECK constraints in CONSTRAINT clauses. Something like:

CREATE TABLE student(
    StudentID INT NOT NULL,
    Name VARCHAR(30) NOT NULL,
    Age INT NOT NULL,
    GENDER VARCHAR(9),
    CONSTRAINT PK_student PRIMARY KEY(StudentID),
    CONSTRAINT CK_student_age check(Age >= 17)
    );

(Also fixed column name in PK, thanks to jarlh)

---

Related Topics