Design data integrity. This objective may include but is not limited to: primary key, foreign key, check constraint, default constraint, NULL/NOT NULL, unique constraint, DML triggers
This section appears to be all about how it is that you enforce data integrity in the database (See Also, Data Integrity). We make sure that columns correspond to look up tables, that there is data where there is supposed to be, and that the data which is there follows the required domain values or business rules.
A Primary Key is a column, or group of columns, which uniquely identify the row of the table. A table can have only ONE primary key, and this is used to enforce the entity integrity of that table. Primary keys cannot accept NULL values for any of the fields which are part of the key.
Behind the scenes, the database engine creates a unique index for enforcing the primary key.
A Foreign Key is a column, or group of columns, which identifies a link between the current record and a record in another table (it can also link to another record in the same table, in a self-referencing table). A foreign key will need to link to either a primary key, or the subject columns of a unique index, on the table it is referencing. The foreign key can contain one or more NULL values; however, if there is a single NULL value the rest of the columns will not be checked.
A foreign key is used to enforce referential integrity between the tables. Therefore, a record cannot be modified or deleted in such a way as to break the reference of the foreign key.
Check Constraints are used to enforce domain integrity by limiting the value which a column will accept to the defined values. Check constraints are determined from a Boolean expression pertaining to the column in question. Multiple check constraints can be placed on a single column. A table level check constraint can be used to check validity against multiple columns.
Note that for a check constraints to fail, it must evaluate to false. This means that a NULL value which returns an UNKNOWN result will potentially successfully evaluate in spite of the constraint. Also, there MUST be a record to check against, otherwise the constraint is not evaluated. Finally, check constraints are not evaluated when deleting a record. As such, table level constraints must be handled with care.
Every column in the database MUST contain a value, even if that value is NULL. A default definition (default constraint) will instruct the database engine to insert the provided value if no value is specified when inserting a record. These default values can be helpful for when you are loading data, but it is not possible to enter the value for the column at that point in time, or the data for that value does not yet exist.
It is relatively common to put in values of zero for numeric columns, or N/A for string columns where no value is specified, and today’s date and time for date time columns. Since you can use some functions inside of these constraints, it is also common to see the user name for the connection inserting the data to be a default value for some audit columns, such as an Inserted By or Updated By column. It should be noted that when you are inserting a record, you can use the DEFAULT VALUES clause of the insert statement to automatically use the default values.
NULL / NOT NULL
A NULL value generally means that we do not know the answer to what the data should provide, it is an unknown. The decision to allow NULL values in a column will affect many aspects of how you deal with the table in which the values are allowed. If a NULL value is allowed in the column, it will make querying and updating the table a bit more complex, as you have to code for the possibility that the data is unknown. Furthermore, other options are not permitted on columns which allow NULL values (i.e. Identity column, Primary Key constraint). When a record is inserted into a table which allows NULL values in one or more columns, and no value is specified for those columns, the record is inserted with the value of NULL (unless there is also a default value set for that column). If the column does not allow for a NULL value for a column, the column MUST be populated when modifying the data.
Unique Constraints are similar to primary key constraints in that they do not allow duplicate values in the column, or group of columns, for which the constraint is defined. However, unlike a primary key, there can be more than one unique constraint per table, and these constraints can be used on columns other than the primary key. Furthermore, the unique constraint columns may contain null values (providing that the null value will produce a record in the table which meets the unique constraint).
A unique constraint can be referenced by a Foreign Key Constraint.
Besides constraints, DML Triggers are the other mechanism for enforcing business rules within SQL Server. DML Triggers are called in response to DML events, such as INSERT, UPDATE or DELETE statements. The statement itself, as well as the trigger, are considered to be in the same transaction, and can thus be rolled back from within the trigger.
DML triggers can be used to enforce complex business logic to enforce the business rules of the company. They can also be used to cascade changes. Unlike check constraints, DML triggers can further reference data in other tables. DML triggers have the ability to inspect the state of the data before and after modification. There can be multiple DML triggers on a single table (even running against the same type of event).
Note that Constraints are checked after the INSTEAD OF trigger stage, but before the AFTER trigger stage. See Triggers Compared to Constraints for a more complete discussion of the differences between the triggers and constraints.