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 can 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
See also
Objects in a PDM