Database Reference
In-Depth Information
4.3.2 Constraints
When creating a table, you also need to specify constraints for some of the columns. Constraints
can be used to keep the integrity of your database, deine the range of values to be entered into
a column, and specify the properties of a column. Let us take a look at the commonly used
constraints.
1.
Referential Constraints
: Referential constraints are used to keep the relationship between
tables. he commonly used foreign key constraint and primary key constraint belong to
referential constraints.
a.
Primary Key Constraint
: his constraint is used to specify a column or a group of col-
umns as a primary key. here are two ways to deine a primary key. If the primary key
is deined on a single column, you can specify the column as a primary key without the
keyword CONSTRAINT. For example, to make the column ProductID the primary
key, use the SQL statement:
ProductID INT PRIMARY KEY
A constraint enforced on a single column is classiied as a column constraint.
If a primary key is deined on multiple columns, it is called a combination primary key
and you need to use the keyword CONSTRAINT to deine it. he syntax for deining
a primary key by using the keyword CONSTRAINT is
CONSTRAINT constraint_name PRIMARY KEY (multiple_column_names)
To illustrate the use of the keyword CONSTRAINT, consider the example that deines
the combination primary key on the columns OrderID and InventoryID:
CONSTRAINT OrderID_InventoryID_pk PRIMARY KEY (OrderID, InventoryID)
If a constraint is enforced on multiple columns, it is called a table constraint. When
more than one column is included in a constraint, you should use the table constraint.
b.
Foreign Key Constraint
: A foreign key constraint can be deined on a single column or
multiple columns. When deining it on a single column, you can use either an unnamed
or a named constraint. For an unnamed foreign key constraint, consider the following
example that makes the column ProductID the foreign key:
FOREIGN KEY (ProductID) REFERENCES PRODUCT (ProductID)
he word ProductID after the keyword phrase FOREIGN KEY is the foreign key col-
umn name in the child table. he parent table name and the primary key column in the
parent table are placed behind the keyword REFERENCES. You can also use a named
foreign key constraint by using the keyword CONSTRAINT. When a foreign key is
deined on multiple columns, you should use a named foreign key constraint. he syntax
for the named foreign key constraint is
CONSTRAINT constraint_name FOREIGN KEY (fk_column_name)
REFERENCES parent_table_name (primary_key_column)
he constraint name is a character string. To make the name meaningful, you can name
the constraint as TableName_ColumnName_fk. he keyword phrase FOREIGN KEY
