Defining Data Integrity

Primary Key Constraints:A primary key constraint enforces uniqueness of rows and also disallows NULLs in the constraint attributes.

ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY(empid);

Unique Constraints:A unique constraint enforces uniqueness of rows, allowing you to implement the concept of alternate keys from the relational model in your database. Unlike primary keys, multiple unique constraints can be defined in the same table. Also, a unique constraint is not restricted to columns defined as NOT NULL.

ALTER TABLE dbo.Employees
ADD CONSTRAINT UNQ_Employees_ssn UNIQUE(ssn);

 

Foreign Key Constraints:A foreign key enforces referential integrity. This constraint is defined on a set of attributes in what's called the referencing table, and points to a set of candidate key (primary key or unique constraint) attributes in what's called the referenced table. Note that the referencing and referenced tables can be one and the same.

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
  orderid   INT         NOT NULL,
  empid     INT         NOT NULL,
  custid    VARCHAR(10) NOT NULL,
  orderts   DATETIME    NOT NULL,
  qty       INT         NOT NULL,
  CONSTRAINT PK_Orders
  PRIMARY KEY(OrderID)
);

ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Employees
FOREIGN KEY(empid)
REFERENCES dbo.Employees(empid);

Note that NULLs are allowed in the foreign key columns even if there are no NULLs in the referenced candidate key columns.

Check Constraints:A check constraint allows you to define a predicate that a row must meet to enter the table or to be modified. For example, the following check constraint ensures that the salary column in the Employees table will support only positive values:

ALTER TABLE dbo.Employees
ADD CONSTRAINT CHK_Employees_salary
CHECK(salary > 0);

Default Constraints:A default constraint is associated with a particular attribute. It is an expression that is used as the default value when an explicit value is not specified for the attribute when you insert a row. For example, the following code defines a default constraint for the orderts attribute (representing the order's timestamp):

ALTER TABLE dbo.Orders
ADD CONSTRAINT DFT_Orders_orderts
DEFAULT(CURRENT_TIMESTAMP) FOR orderts;

The default expression invokes the CURRENT_TIMESTAMP function, which returns the current date and time value. Once this default expression is defined, whenever you insert a row in the Orders table and do not explicitly specify a value in the orderts attribute, SQL Server will set the attribute value to CURRENT_TIMESTAMP.