A database constraint is a rule that limits which values or data operations a table accepts. Constraints protect relationships and prevent inconsistent data, much like validation rules in a spreadsheet. section 19.1 section 17.2.3
Common constraint tasks include:
- Define a PRIMARY KEY so each row has a unique identity. section 18.6
- Define a FOREIGN KEY so related columns point to valid rows in another table. section 18.6
- Add constraints to an existing table with ALTER TABLE, or declare them while creating a table with CREATE TABLE. section 19.1
- Name important constraints explicitly; inline declarations are simpler but let the RDBMS generate the name. section 19.1
Repairing a duplicate key
Suppose orderitem should use OrderID and OrderItem as its primary key. Creating that constraint fails because OrderID 1022 and OrderItem 1 appear twice. Query the conflicting rows, change one incorrect OrderItem value to 2, and then create the primary key. The different TitleID values indicate that the rows are separate items rather than duplicates. section 18.7
ALTER TABLE author
ADD CONSTRAINT PK_author PRIMARY KEY (AuthorID);
ALTER TABLE customer
ADD CONSTRAINT PK_customer PRIMARY KEY (CustomerID);
ALTER TABLE orderheader
ADD CONSTRAINT PK_orderheader PRIMARY KEY (OrderID);
ALTER TABLE promotion
ADD CONSTRAINT PK_promotion PRIMARY KEY (PromotionID);
ALTER TABLE title
ADD CONSTRAINT PK_title PRIMARY KEY (TitleID);
ALTER TABLE titleauthor
ADD CONSTRAINT PK_titleauthor PRIMARY KEY (TitleID, AuthorID);
Use the SQL listing as a pattern for adding named PRIMARY KEY constraints, including the composite key on titleauthor. section 18.7
Constraints can also affect deletion. DELETE without a WHERE clause can remove every row, while TRUNCATE TABLE removes all rows more directly; either operation may fail when constraints prohibit it. section 17.2.3
