Database Reference
In-Depth Information
indicates that the constraint is a type of foreign key. he keyword REFERENCES speci-
ies the name of the parent table whose primary key column is used as the foreign key
column in the child table. For example, if you want to specify the column ItemID as a
foreign key, use the SQL statement below:
CONSTRAINT Inventory_ItemId_fk FOREIGN KEY (ItemID)
REFERENCES ITEM (ItemID)
As indicated in the above SQL statement, the constraint name is Inventory_ItemId_fk,
which should be unique in the database. he name of the foreign key column is ItemID.
he parent table is ITEM, and the primary key column in the parent table is ItemID.
As you can see in the above SQL statement, two tables are connected by a foreign key
constraint. If the primary key column in the parent table is referenced by a foreign key
in the child table, its content values cannot be deleted or changed. If you really need
to delete or change a value of the primary key referenced by a foreign key, you have to
delete the foreign key constraint irst and then make a change.
2. Domain Constraints: A domain constraint determines the values to be entered into a col-
umn. Constraints such as CHECK, NOT NULL, and UNIQUE are domain constraints.
a.
CHECK Constraint
: A CHECK constraint can be used to limit the values entered into
a column to enforce domain integrity. he syntax of the constraint is shown below:
CONSTRAINT constraint_name CHECK(constraint_conditions)
Logical operators such as AND or OR can be used to enforce multiple conditions. As
an example, let us include only the values that are greater or equal to zero in the column
Quantity. he SQL statement is given below:
CHECK(Quantity
>=
0)
Or, we can use the named CHECK constraint shown below:
CONSTR AINT Quantity_ck CHECK(Quantity
>=
0)
b.
UNIQUE Constraint
: A UNIQUE constraint can be used to enforce uniqueness
on nonprimary key columns. For example, we can make the values in the column
ProductName unique with the following SQL statement:
CONSTR AINT ProductName_unique UNIQUE(ProductName)
You can also use an unnamed unique constraint by using the keyword UNIQUE.
c.
DEFAULT Constraint
: his constraint speciies the default value for a column. For
example, suppose that you want the default value for the column Quantity be 0; you can
do this by using the following SQL statement:
Quantity INT DEFAULT 0
d.
NOT NULL Constraint
: his constraint can be used to prevent null values in a col-
umn. If you want to make sure that the column has no null value, use the following SQL
statement:
OrderDate DATETIME NOT NULL
