Constraints on Eval Table Columns

SQL Server has five constraints (NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK) available to enforce the integrity of data in a database. The Eval database uses all five of these constraints as follows:

• A NOT NULL constraint specifies that a column cannot contain a null value. The database designers have used this constraint throughout the Eval database. You create each constraint by clearing the Allow Nulls attribute for the column. The Activity table is an example of a table in the Eval database using the NOT NULL constraint. This constraint does not have a name and it is not listed in the Table and Index dialog box for the table in SQL Server Enterprise Manager.
• A PRIMARY KEY constraint enforces the uniqueness of primary key values in a table. Because all tables in the Eval database have a primary key, all tables have a PRIMARY KEY constraint. This constraint is automatically created when you assign a primary key attribute to a column or columns and save the changes to the table. The system-assigned name is the concatenation of PK_ and the name of the field; for example, the default name of the primary key constraint on the Activity table is PK_Activity. You can modify the properties of a PRIMARY KEY constraint on the Relationship tab of the Table and Index dialog box in SQL Server Enterprise Manager or by running an SQL statement.
• A FOREIGN KEY constraint defines the relationship between tables and maintains data integrity when the user attempts to add, change, or delete records in a table. Creating Relationships Between Eval Tables and Adding Attributes to Eval Tables describe how the Eval database uses foreign keys. This constraint is automatically created when you create a relationship between tables in the Edit Diagram window in SQL Server Enterprise Manager and save the changes, or when you run an SQL statement to create the relationship. The name assigned to the constraint is the concatenation of FK_ and the names of the two tables you are linking; for example, the FOREIGN KEY constraint between the Activity table and the PersonActivity table is named FK_PersonActivity_Activity. Subsequent FOREIGN KEY constraint default names for the same two tables append a counter starting with the number 1; for example, a subsequent constraint between the Activity table and the PersonActivity table is named FK_PersonActivity_Activity1. You can modify the properties of a FOREIGN KEY constraint on the Relationship tab of the Table and Index dialog box in SQL Server Enterprise Manager or by running an SQL statement.
• A UNIQUE constraint enforces the uniqueness of a column or set of columns in a table. The column or columns that make up a candidate key (an alternative primary key) often have a UNIQUE constraint. The combination of the PersonId, PersonType, and GroupId columns in the PersonGroup table have UNIQUE constraints. You can create and modify a UNIQUE constraint on the Indexes/Key tab of the Table and Index dialog box in SQL Server Enterprise Manager or by running an SQL statement. The name the system assigns is the concatenation of XI_ and the name of the table; for example, the default name of the first unique constraint on the Activity table is XI_Activity. Subsequent UNIQUE constraint default names for the same table append a counter starting with the number 1. You can modify the properties of a UNIQUE constraint on the Relationship tab of the Table and Index dialog box in SQL Server Enterprise Manager or by running an SQL statement.
• A CHECK constraint enforces integrity by limiting the values that can be placed in a column. The PersonGroup table has a CHECK constraint that allows only values of A, E, or I to be inserted in the PersonType column. You can create and modify a CHECK constraint on the Tables tab of the Table and Index dialog box in SQL Server Enterprise Manager or by running an SQL statement. The name the system assigns is the concatenation of CK_ and the name of the table; for example, the default name of the first CHECK constraint on the Activity table is CK_Activity. Subsequent CHECK constraint default names for the same table append a counter starting with the number 1. You can modify the properties of a CHECK constraint on the Relationship tab of the Table and Index dialog box in SQL Server Enterprise Manager or by running an SQL statement. SQL Server Rule objects can be alternatives to CHECK constraints, but no rules are defined on the Eval database. You should consider rules as a backward compatibility feature. The PT design team uses CHECK constraints instead of rules because the team can apply multiple CHECK constraints to a column but can apply only one rule to a column.

Note  SQL Server automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint.