Department of Information Technology
S.Y. BTech (IT)
SUB: DBMS LAB
Experiment No: 7
Course Outcome: Construct SQL queries to perform operations on the database
Aim: To implement 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 the specified column(s). For example, a
UNIQUE or PRIMARY KEY constraint prevents you from inserting a value that is a
duplicate of an existing value; a CHECK constraint prevents you from inserting a value
that does not match a search condition; and a FOREIGN KEY constraint enforces a link
between data in two tables.
Introduction to Integrity Constraints
Constraints enable you to automatically enforce the integrity of a database. Constraints
define rules regarding the values allowed in columns and are the standard mechanisms
for enforcing integrity.
Constraints can be column constraints or table constraints:
A column constraint is specified as part of a column definition and applies only to
that column.
A table constraint is declared independently from a column definition and can
apply to more than one column in a table.
Four main classes of constraints: PRIMARY KEY constraints, UNIQUE constraints,
FOREIGN KEY constraints, and CHECK constraints.
PRIMARY KEY Constraints
A table usually has a column (or combination of columns) whose values uniquely
identify each row in the table. This column (or columns) is called the primary key of the
table and enforces the entity integrity of the table. You can create a primary key by
defining a PRIMARY KEY constraint when you create or alter a table. A table can have
only one PRIMARY KEY constraint, and a column that participates in the PRIMARY
KEY constraint cannot accept null values.
If a PRIMARY KEY constraint is defined for more than one column, values can be
duplicated within one column—but each combination of values from all of the columns
in the PRIMARY KEY constraint definition must be unique.
1
Department of Information Technology
The primary key of the TitleAuthor table in the Pubs database.
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
Syntax:
CREATE TABLE Table1
(
Col1 INT PRIMARY KEY,
Col2 VARCHAR(30)
)
CREATE TABLE Table1
(
Col1 INT,
Col2 VARCHAR(30),
CONSTRAINT table_pk PRIMARY KEY (Col1)
)
You can use the ALTER TABLE statement to add a PRIMARY KEY constraint to an
existing table:
ALTER TABLE Table1
ADD CONSTRAINT table_pk PRIMARY KEY (Col1)
When a PRIMARY KEY constraint is added to an existing column (or columns) in the
table, Oracle checks the existing data in the columns to ensure that it follows the rules for
primary keys:
No null values
No duplicate values
2
Department of Information Technology
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.
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.
You can use the same 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. As with PRIMARY KEY constraints, a UNIQUE constraint can be
modified or deleted once it has been created.
When a UNIQUE constraint is added to an existing column (or columns) in the table,
SQL Server 2000 (by default) checks the existing data in the columns to ensure that all
values, except null, are unique. If a UNIQUE constraint is added to a column that has
duplicated values, SQL Server returns an error and does not add the constraint.
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.
3
Department of Information Technology
You can create a foreign key by defining a FOREIGN KEY constraint when you create
or alter a table. In addition to a PRIMARY KEY constraint, a FOREIGN KEY constraint
can reference the columns of a UNIQUE constraint in another table.
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
The following CREATE TABLE statement creates the Table1 table and defines the Col2
column with a FOREIGN KEY constraint that references the EmployeeID column, which
is the primary key in the Employees table:
CREATE TABLE Table1
(
Col1 INT PRIMARY KEY,
Col2 INT REFERENCES Employees(EmployeeID)
)
You can also define the same constraint by using a table-level FOREIGN KEY
constraint:
CREATE TABLE Table1
(
Col1 INT PRIMARY KEY,
Col2 INT,
CONSTRAINT col2_fk FOREIGN KEY (Col2)
REFERENCES Employees (EmployeeID)
)
You can use the ALTER TABLE statement to add a FOREIGN KEY constraint to an
existing table:
ALTER TABLE Table1
ADD CONSTRAINT col2_fk FOREIGN KEY (Col2)
REFERENCES Employees (EmployeeID)
When a FOREIGN KEY constraint is added to an existing column (or columns) in the
table, checks the existing data in the columns to ensure that all values, except null values,
exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint. You can
4
Department of Information Technology
prevent SQL Server from checking the data in the column against the new constraint,
however, and force it to add the new constraint regardless of the data in the column. This
option is useful when the existing data already meets the new FOREIGN KEY constraint
or when a business rule requires the constraint to be enforced only from this point
forward.
CHECK Constraints
CHECK constraints enforce domain integrity by limiting the values that are accepted by a
column. They are similar to FOREIGN KEY constraints in that they control the values
that are placed in a column. The difference is in how they determine which values are
valid. FOREIGN KEY constraints get the list of valid values from another table, and
CHECK constraints determine the valid values from a logical expression that is not based
on data in another column. For example, it is possible to limit the range of values for a
salary column by creating a CHECK constraint that allows only data ranging from
$15,000 through $100,000. This feature prevents the entering of salaries from outside the
normal salary range.
You can create a CHECK constraint with any logical (Boolean) expression that returns
TRUE or FALSE based on the logical operators. To allow only data that ranges from
$15,000 through $100,000, the logical expression is as follows:
salary >= 15000 AND salary <= 100000
You can apply multiple CHECK constraints to a single column. The constraints are
evaluated in the order in which they are created. In addition, you can apply a single
CHECK constraint to multiple columns by creating it at the table level.
Creating CHECK Constraints
You can create a CHECK 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
The following CREATE TABLE statement creates the Table1 table and defines the Col2
column with a CHECK constraint that limits the column-entered values to a range
between 0 and 1000:
CREATE TABLE Table1
(
Col1 INT PRIMARY KEY,
Col2 INT
CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000),
Col3 VARCHAR(30)
)
5
Department of Information Technology
You can also define the same constraint by using a table-level CHECK constraint:
CREATE TABLE Table1
(
Col1 INT PRIMARY KEY,
Col2 INT,
Col3 VARCHAR(30),
CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000)
)
You can use the ALTER TABLE statement to add a CHECK constraint to an existing
table:
ALTER TABLE Table1
ADD CONSTRAINT limit_amount CHECK (Col2 BETWEEN 0 AND 1000)
When a CHECK constraint is added to an existing table, the CHECK constraint can apply
either to new data only or to existing data as well. By default, the CHECK constraint
applies to existing data as well as to any new data. The option of applying the constraint
to new data only is useful when the existing data already meets the new CHECK
constraint or when a business rule requires the constraint to be enforced only from this
point forward.
6
Department of Information Technology
Practical Questions:
1. Create a table with a name Sales_Order having columns order_no as primary key,
order_date should not be a null value, client_no, order_status, salesman_no.
2. Insert the records in the table in such a way that few records should show constraint
violation for the columns order_no & order_date.
3. Display all the records of the Sales_Order table.
4. Add the constraint to the Sales_Order table that client_no column should not have
duplicate values & also it should allow null values to be inserted.
5. Display all the records of the Sales_Order table.
6. Create a table with a name Client_Master having columns client_no as a primary key,
name, address, city, pincode, order_no as foreign key referencing Sales_Order
order_no.
7. Insert the records in the Client_Master table in such a way that few records should
show constraint violation for the column order_no.
8. Display all the records of the Client_Master table.
9. Delete a record from the Client_Master table whose client_no is 1
10. Delete a record from the Sales_Order table whose order_no is 2.
11. Update any one value of the order_no column to a new value of Sales_Order table.
12. Create a table with name Client_Master1 having columns client_no as primary key,
name, city & balance. Names starting with ‘a’, city should be either Mumabi or Delhi
& balance should be greater than 1000.
13. Insert the records in the table.
14. Display all the records of the table.
Questions for Observation Sheet
Consider the case study chosen in experiment 1. Write create table command for any
three tables considering different integrity constraint.