Illegal column-level constraint. (Error 3789)

This error occurs when using the CREATE TABLE or ALTER TABLE ALTER COLUMN syntax. While ANSI SQL allows for creating CHECK constraints as part of the table definition, the Microsoft Access database engine requires that the user create the CHECK constraint separate from the COLUMN definition. This can be accomplished by issuing the CHECK keyword after a comma. For example, the following syntax will work because the CHECK constraint is defined separately from the column and follows a comma:

CREATE TABLE Orders (OrderId IDENTITY (100,10) CONSTRAINT pkOrders PRIMARY KEY, CustId LONG CONSTRAINT fkCustomersCustId REFERENCES Customers (CustId), Balance DOUBLE, CONSTRAINT CustomerExceededCreditLimit CHECK (CustId IN (SELECT CustId FROM Customers C WHERE C.CustId = Orders.CustId AND C.CreditLimit >= (SELECT SUM(Balance)FROM Orders O WHERE O.CustId = Orders.CustId))));.



Community Additions

Show:
© 2014 Microsoft