Check constraint

Architecting and designing : Data modeling : The physical data model : Objects in a PDM : Check constraint

Check constraint

Check constraints enforce domain integrity by limiting the values that are accepted by a column. Check constraints determine the valid values from a logical expression that is not based on data in another column. For example, to limit the range of values for a salary column, one might create a check constraint that allows only data that ranges from $15,000 through $100,000.

salary >= 15000 AND salary <= 100000

In System Architect, check constraints can be written for tables, columns and user-defined data types (UDT).

To expand the usefulness of a check constraint, you may include a macro:

Note Every macro has “its own definition”; they cannot be used indiscriminately.

#_pfxn

Expands to the column name

Example

UDT: #_pfxn > 100Check constraint: AAA > 100 BBB > 100

#upper()

Converts the value between the parenthesis to upper case

Example

Column: AAA <> #upper(`one two three')Check constraint: AAA <> `ONE TWO THREE'

#__name

(Contains two underscores __) Expands to the table name.

Example

Column or UDT:

constraint #__name_#_pfxn check (#_pfxn <> 1000)Check constraint: constraint CUSTOMER_CUSTID check (CUSTID <> 1000)

Table Check Constraint definition example

Control the allowed value of one column in the table based on the value of another column in the same table.

Generated SQL DDL

CREATE TABLE CUST_ORDER
(
ORDER_NUMBER numeric(10) NOT NULL,
ORDER_DATE datetime NOT NULL,
CUSTOMER_ID character(10) NOT NULL,
TOTAL_VALUE numeric(5) NOT NULL,
MAX_CARD_VALUE numeric(6) NOT NULL,
CONSTRAINT ORDER_PK
PRIMARY KEY NONCLUSTERED (ORDER_NUMBER,CUSTOMER_ID),
CHECK (TOTAL_VALUE < MAX_CARD_VALUE)
)
go

Column Check Constraint example

Control the name of the check constraint by appending the name of the table to the name of the column.

Generated SQL DDL

CREATE TABLE CUSTOMER
(
CUSTOMER_ID character(10) NOT NULL,
CUSTOMER_NAME character(55) NOT NULL
constraint CUSTOMER_CUSTOMER_NAME check (CUSTOMER_NAME <> 'Enron',
CUSTOMER_CITY CITY NOT NULL
CHECK (CUSTOMER_CITY = 'New York'),
CONSTRAINT CUSTOMER_PK
PRIMARY KEY NONCLUSTERED (CUSTOMER_ID)
)
go

UDT Check Constraint example

Control the value of columns in many tables through the UDT.

Generated SQL DDL

sp_addtype STANDARD_ID, 'character(10)','NOT NULL'
go

CREATE TABLE CUSTOMER
(
CUSTOMER_ID STANDARD_ID NOT NULL
constraint CUSTOMER_CUSTOMER_ID check (CUSTOMER_ID > 1000),
CUSTOMER_NAME character(55) NOT NULL
constraint CUSTOMER_CUSTOMER_NAME check (CUSTOMER_NAME <> 'Enron',
CUSTOMER_CITY CITY NOT NULL
CHECK (CUSTOMER_CITY = 'New York'),
CONSTRAINT CUSTOMER_PK
PRIMARY KEY NONCLUSTERED (CUSTOMER_ID)
)
go

CREATE TABLE SALESPERSON
(
EMPLOYEE_ID STANDARD_ID NOT NULL
constraint SALESPERSON_EMPLOYEE_ID check (EMPLOYEE_ID > 1000),
SALES_CITY CITY NOT NULL
CHECK (SALES_CITY = 'New York'),
CONSTRAINT SALESPERSON_PK
PRIMARY KEY NONCLUSTERED (EMPLOYEE_ID)
)
go