lOMoARcPSD|39225634
Implementing Integrity Constraints
Intro To Database Appl (Montgomery College)
Scan to open on Studocu
Studocu is not sponsored or endorsed by any college or university
Downloaded by White Devil (
[email protected])
lOMoARcPSD|39225634
Implementing Integrity Constraints
A constraint is a property assigned to a table or a column within a table that prevents invalid data values
from being placed in specified column(s).
Examples include UNIQUE, constraints, PRIMARY KEY constraints and FOREIGN KEY constraints
Creating PRIMARY KEY Constraints
You can create a PRIMARY KEY constraint by using one of the following methods:
Creating the constraint when the table is created (as part of the table definition)
Adding the constraint to an existing table, provided that no other PRIMARY KEY constraint already exists.
PRIMARY KEY -EXAMPLE
CREATE TABLE Table1 (col1 int PRIMARY KEY, col2 varchar(30) )
PRIMARY KEY – table level constraint
CREATE TABLE Table1 (col1 int , col2 varchar(30), CONSTRAINT table_pk PRIMARY KEY (col1) )
Composite Key
CREATE TABLE FactoryProcess ( EventType INT, EventTime DATETIME, EventSite CHAR(50),
EventDesc CHAR(1024), CONSTRAINT event_key PRIMARY KEY (EventType, EventTime) )
Adding a Primary to an Existing Table
ALTER TABLE Table1 ADD CONSTRAINT table_pk PRIMARY KEY (col1)
UNIQUE CONSTRAINTS
You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that
do not participate in a primary key.
Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, you should use a
UNIQUE constraint instead of a PRIMARY KEY constraint in the following situations:
If a column (or combination of columns) is not the primary key. Multiple UNIQUE constraints can be
defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.
If a column allows null values. UNIQUE constraints can be defined for columns that allow null values,
whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values.
A UNIQUE constraint can also be referenced by a FOREIGN KEY constraint.
Creating UNIQUE Constraints
You can create a UNIQUE constraint in the same way that you create a PRIMARY KEY constraint:
By creating the constraint when the table is created (as part of the table definition)
By adding the constraint to an existing table, provided that the column or combination of columns
comprising the UNIQUE constraint contains only unique or NULL values. A table can contain multiple
UNIQUE constraints.
lOMoARcPSD|39225634
You can use the same Transact-SQL statements to create a UNIQUE constraint that you used to create a
PRIMARY KEY constraint.
Simply replace the words PRIMARY KEY with the word UNIQUE.
FOREIGN KEY Constraints
A foreign key is a column or combination of columns used to establish and enforce a link between the
data in two tables.
Create a link between two tables by adding a column (or columns) to one of the tables and defining
those columns with a FOREIGN KEY constraint.
The columns will hold the primary key values from the second table. A table can contain multiple
FOREIGN KEY constraints.
Creating FOREIGN KEY Constraints
You can create a FOREIGN KEY constraint by using one of the following methods:
Creating the constraint when the table is created (as part of the table
definition)
Adding the constraint to an existing table, provided that the FOREIGN KEY constraint is linked to an
existing PRIMARY KEY constraint or a UNIQUE constraint in another (or the same) table
CREATE TABLE Table1 ( Col1 INT PRIMARY KEY, Col2 INT REFERENCES Employees(EmployeeID)
)
table-level FOREIGN KEY constraint
CREATE TABLE Table1 ( Col1 INT PRIMARY KEY, Col2 INT, CONSTRAINT col2_fk FOREIGN KEY (Col2)
REFERENCES Employees (EmployeeID) )
ALTER TABLE statement to add a FOREIGN KEY
ALTER TABLE Table1
ADD CONSTRAINT col2_fk
FOREIGN KEY (Col2)
REFERENCES Employees (EmployeeID)
