When you update a table using the Query Designer, the query respects any constraint defined in the database for the columns and tables that you are updating. For example, if a table is defined with a unique constraint, you cannot execute an Update or Append query that would write a non-unique row to the table. Similarly, if you are updating a column that has a constraint limiting its values to a certain range, the query will result in an error if you provide an update value outside the specified range.
Queries also respect referential integrity defined between tables. For example, you cannot add rows to a table in the "many" side of a one-to-many relationship if the corresponding row in the "one" side does not exist.
A primary key constraint ensures no duplicate values are entered in particular columns and that NULL values are not entered in those columns. You can use primary key constraints to enforce uniqueness as well as referential integrity. For example, the au_id column uniquely identifies each author stored in the authors table.
You create primary key constraints directly in a database diagram.
A foreign key constraint works in conjunction with primary key or unique constraints to enforce referential integrity among specified tables. For example, you can place a foreign key constraint on the title_id column in the publishers table to ensure that a value entered in that column matches an existing value in the title_id column of the titles table.
In database diagrams, a foreign key constraint is automatically placed on specified columns when you create a relationship to a table from another table to which a primary key or unique constraint is attached.
A check constraint specifies the data values or formats that are acceptable in one or more columns in a table. For example, you can require the zip column of the authors table to allow only five-digit numeric entries.
You can define many check constraints for a table. You use the Constraints property page to create, modify, or delete each check constraint.
Overview of check constraint expressions
When you attach a check constraint to a table or column, you must include an SQL expression.
You can create a simple constraint expression to check data for a simple condition; or you can create a complex expression, using Boolean operators, to check data for several conditions. For example, suppose the authors table has a zip column where a 5-digit character string is required. This sample constraint expression guarantees that only 5-digit numbers are allowed:
zip LIKE '[0-9][0-9][0-9][0-9][0-9]'
Or suppose the sales table has a column called qty which requires a value greater than 0. This sample constraint guarantees that only positive values are allowed:
qty > 0
Or suppose the orders table limits the type of credit cards accepted for all credit card orders. This sample constraint guarantees that if the order is placed on a credit card, then only Visa, MasterCard, or American Express is accepted:
NOT (payment_method = ‘credit card’) OR
(card_type IN (‘VISA’, ‘MASTERCARD’, ‘AMERICAN EXPRESS’))
Defining a constraint expression
In the Check Constraints tab of the property page, type an expression in the Constraint expression box using the following syntax:
{constant | column_name | function | (subquery)}
[{operator | AND | OR | NOT}
{constant | column_name | function | (subquery)}…]
The SQL syntax is made up of the following parameters:
| Parameter | Description |
|---|---|
| constant | A literal value, such as numeric or character data. Character data must be enclosed within single quotation marks ('). |
| column_name | Specifies a column. |
| function | A built-in function. For details about functions, see the SQL Server documentation. |
| operator | An arithmetic, bitwise, comparison, or string operator. For details about operators, see the SQL Server documentation. |
| AND | Use in Boolean expressions to connect two expressions. Results are returned when both expressions are true.
When AND and OR are both used in a statement, AND is processed first. You can change the order of execution by using parentheses. |
| OR | Use in Boolean expressions to connect two or more conditions. Results are returned when either condition is true.
When AND and OR are both used in a statement, OR is evaluated after AND. You can change the order of execution by using parentheses. |
| NOT | Negates any Boolean expression (which can include keywords, such as LIKE, NULL, BETWEEN, IN, and EXISTS).
When more than one logical operator is used in a statement, NOT is processed first. You can change the order of execution by using parentheses. |
A unique constraint ensures no duplicate values are entered into specified columns that are not a table’s primary key. For example, in the employee table in which the emp_id column is the primary key, you can define a unique constraint that requires entries in the Social Security number (ssn) column to be unique within the table.
In database diagrams, you use the Indexes/Keys property page to create, modify, or delete unique constraints.
A default constraint enables you to define the value that will be supplied for a column whenever a user fails to enter a value. For example, in a table with a column called payterms, you can instruct your database server to enter "???" or "fill in later" if the user leaves it blank.
In database diagrams, you define a default constraint as a property of a column in your table. You define this type of constraint for a column by specifying a default value inside a table in column properties view. Be sure to specify the constraint with the correct delimiters. For example, strings must be surrounded with single quotes.