Create a constraint (ADP)

Define a primary key to enforce uniqueness for values entered in specified columns that do not allow nulls. If you define a primary key for a table in your database, you can relate that table to other tables, thus reducing the need for redundant data. A table can have only one primary key.

1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
2. In your database diagram, click the row selector for the database column you want to define as the primary key. If you want to select multiple columns, hold down the CTRL key while you click the row selectors for the other columns.
3. Right-click the row selector for the column and select Primary Key. A primary key index, named "PK_" followed by the table name, is automatically created; you can find it on the Indexes/Keys tab of the Properties page.

   Warning   If you want to redefine the primary key, any relationships to the existing primary key must be deleted before the new primary key can be created. A message will warn you that existing relationships will be automatically deleted as part of this process.

A primary key column is identified by a primary key symbol in its row selector.

If a primary key consists of more than one column, duplicate values are allowed in one column, but each combination of values from all the columns in the primary key must be unique.

If you define a compound key, the order of columns in the primary key matches the order of columns as shown in the table in your database diagram. However, you can change the order of columns after the primary key is created. In the Column name grid, remove the columns from the primary key. Then add the columns back in the order you want. To remove a column from the key, simply remove the column name from the Column name list.

Attach a check constraint to a table to specify the data values that are acceptable in one or more columns.

1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
2. In your database diagram, right-click the table that will contain the constraint, then select Constraints from the shortcut menu.
3. Choose New. The Selected constraint box displays the system-assigned name of the new constraint. System-assigned names begin with "CK_" followed by the table name.
4. In the Constraint expression box, type the SQL expressions for the check constraint. For example, to limit the entries in the state column of the authors table to New York, type:

   state = 'NY'
   						

   Or, to require entries in the zip column to be 5 digits, type:

   zip LIKE '[0-9][0-9][0-9][0-9][0-9]'
   						

   Note   Make sure to enclose any non-numeric constraint values in single quotation marks (').

5. If you want to give the constraint a different name, type the name in the Constraint name box.
6. Use the check boxes to control when the constraint is enforced:
   • To test the constraint on existing data before creating the constraint, check Check existing data on creation.
   • To enforce the constraint whenever a replication operation occurs on this table, check Enforce constraint for replication.
   • To enforce the constraint whenever a row of this table is inserted or updated, check Enforce constraint for INSERTs and UPDATEs.

When you create a check constraint, you can set an option to apply it either to new data only or to existing data as well. The option of applying the constraint to new data only is useful when you know that the existing data already meets the new check constraint, or when a business rule requires the constraint to be enforced only from this point forward.

For example, you may have required zip codes to be limited to five digits in the past, but now want new data to allow nine-digit zip codes. Old data with five-digit zip codes will coexist with new data that contains nine-digit zip codes.

1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.

   -or-

   In your database diagram, right-click the table containing the constraint, then select Constraints from the shortcut menu.

2. Select the constraint from the Selected constraint list.
3. Select the Check existing data on creation check box. This option is selected by default.

The check constraint will be applied when you save the database diagram. If any constraint violations are encountered during the save process, the table cannot be saved.

Create a unique constraint to ensure no duplicate values are entered in specific columns that do not participate in a primary key. While both a unique constraint and a primary key enforce uniqueness, you should attach a unique constraint instead of a primary key constraint to a table if:

• You want to enforce uniqueness in a column or combination of columns. You can attach multiple unique constraints to a table, whereas you can attach only one primary key constraint to a table.
• You want to enforce uniqueness in a column that allows null values. You can attach unique constraints to columns that allow null values, whereas you can attach primary key constraints only to columns that do not allow null values. When you attach a unique constraint to a column allowing null values, you ensure that at most one row will have a null value in the constrained column.

1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
2. In your database diagram, right-click the table that will contain the constraint, then select Indexes/Keys from the shortcut menu.
3. Choose New. A system-assigned name appears in the Index name box.
4. Under Column name, expand the list of columns and select the column that you want to attach the constraint to. To attach the constraint to multiple columns, select the additional columns in subsequent rows.
5. Select the Create UNIQUE check box.
6. Select the Constraint option.

The unique constraint is created in the database when you save the database diagram.

You can control the sort order of key values and the action taken when duplicate keys exist. To do this, you should create a unique index instead of a unique constraint.