1 of 30

Including Constraints

10

10-1

2 of 30

Objectives

After completing this lesson, you should be able to

do the following:

Describe constraints
Create and maintain constraints

10-2

3 of 30

What are Constraints?

Constraints enforce rules at the table level.
Constraints prevent the deletion of a table if there are dependencies.
The following constraint types are valid:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

10-3

4 of 30

Constraint Guidelines

Name a constraint or the Oracle server generates a name by using the SYS_Cn format.
Create a constraint either:

At the same time as the table is created, or
After the table has been created

Define a constraint at the column or table level.
View a constraint in the data dictionary.

10-4

5 of 30

Defining Constraints

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]

[column_constraint],

...

[table_constraint][,...]);

CREATE TABLE employees(

employee_id NUMBER(6),

first_name VARCHAR2(20),

...

job_id VARCHAR2(10) NOT NULL,

CONSTRAINT emp_emp_id_pk

PRIMARY KEY (EMPLOYEE_ID));

10-5

6 of 30

Defining Constraints

Column constraint level��
Table constraint level

column [CONSTRAINT constraint_name] constraint_type,

column,...

[CONSTRAINT constraint_name] constraint_type

(column, ...),

10-6

7 of 30

The NOT NULL Constraint

Ensures that null values are not permitted for the

column:

NOT NULL constraint

(No row can contain�a null value for�this column.)

Absence of NOT NULL constraint

(Any row can contain�null for this column.)

NOT NULL

constraint

…

10-7

8 of 30

The NOT NULL Constraint

Is defined at the column level:

CREATE TABLE employees(

employee_id NUMBER(6),

last_name VARCHAR2(25) NOT NULL,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

hire_date DATE

CONSTRAINT emp_hire_date_nn

NOT NULL,

...

System

named

User

named

10-8

9 of 30

The UNIQUE Constraint

EMPLOYEES

UNIQUE constraint

INSERT INTO

Not allowed: already exists

Allowed

…

10-9

The UNIQUE Constraint

A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique—that is, no two rows of a table can have duplicate values in a specified column or set of columns. The column (or set of columns) included in the definition of the UNIQUE key constraint is called the unique key. If the UNIQUE constraint comprises more than one column, that group of columns is called a composite unique key.

UNIQUE constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE constraint.

Note: Because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint.

Instructor Note

Explain to students that since the JSMITH e-mail ID already exists after the first insertion, the second entry is not allowed.

10 of 30

The UNIQUE Constraint

Defined at either the table level or the column level:

CREATE TABLE employees(

employee_id NUMBER(6),

last_name VARCHAR2(25) NOT NULL,

email VARCHAR2(25),

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

hire_date DATE NOT NULL,

...

CONSTRAINT emp_email_uk UNIQUE(email));

10-10

11 of 30

The PRIMARY KEY Constraint

DEPARTMENTS

PRIMARY KEY

INSERT INTO

Not allowed

(Null value)

Not allowed

(50 already exists)

…

10-11

12 of 30

The PRIMARY KEY Constraint

Defined at either the table level or the column level:

CREATE TABLE departments(

department_id NUMBER(4),

department_name VARCHAR2(30)

CONSTRAINT dept_name_nn NOT NULL,

manager_id NUMBER(6),

location_id NUMBER(4),

CONSTRAINT dept_id_pk PRIMARY KEY(department_id));

10-12

13 of 30

The FOREIGN KEY Constraint

DEPARTMENTS

EMPLOYEES

FOREIGN�KEY

INSERT INTO

Not allowed�(9 does not exist)

Allowed

PRIMARY�KEY

…

10-13

14 of 30

The FOREIGN KEY Constraint

Defined at either the table level or the column level:

CREATE TABLE employees(

employee_id NUMBER(6),

last_name VARCHAR2(25) NOT NULL,

email VARCHAR2(25),

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

hire_date DATE NOT NULL,

...

department_id NUMBER(4),

CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)

REFERENCES departments(department_id),

CONSTRAINT emp_email_uk UNIQUE(email));

10-14

15 of 30

FOREIGN KEY Constraint �Keywords

FOREIGN KEY: Defines the column in the child table at the table constraint level
REFERENCES: Identifies the table and column in the parent table
ON DELETE CASCADE: Deletes the dependent rows in the child table when a row in the parent table is deleted.
ON DELETE SET NULL: Converts dependent foreign key values to null

10-15

The FOREIGN KEY Constraint (continued)

The foreign key is defined in the child table, and the table containing the referenced column is the parent table. The foreign key is defined using a combination of the following keywords:

FOREIGN KEY is used to define the column in the child table at the table constraint level.

REFERENCES identifies the table and column in the parent table.

ON DELETE CASCADE indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted.

ON DELETE SET NULL converts foreign key values to null when the parent value is removed.

The default behavior is called the restrict rule, which disallows the update or deletion of referenced data.

Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.

16 of 30

The CHECK Constraint

Defines a condition that each row must satisfy
The following expressions are not allowed:

References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows

..., salary NUMBER(2)

CONSTRAINT emp_salary_min

CHECK (salary > 0),...

10-16

The CHECK Constraint

The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as query conditions, with the following exceptions:

References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns

Calls to SYSDATE, UID, USER, and USERENV functions

Queries that refer to other values in other rows

A single column can have multiple CHECK constraints which refer to the column in its definition. There is no limit to the number of CHECK constraints which you can define on a column.

CHECK constraints can be defined at the column level or table level.

CREATE TABLE employees

(...

salary NUMBER(8,2) CONSTRAINT emp_salary_min

CHECK (salary > 0),

...

Instructor Note

Explain what pseudocolumns are. Pseudocolumns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudocolumn. However, you cannot insert into, update, or delete from a pseudocolumn. Pseudocolumns can be used in SQL statements.

17 of 30

Adding a Constraint Syntax

Use the ALTER TABLE statement to:

Add or drop a constraint, but not modify its structure
Enable or disable constraints
Add a NOT NULL constraint by using the MODIFY clause

ALTER TABLE table

ADD [CONSTRAINT constraint] type (column);

10-17

Adding a Constraint

You can add a constraint for existing tables by using the ALTER TABLE statement with the ADD clause.

In the syntax:

table is the name of the table

constraint is the name of the constraint

type is the constraint type

column is the name of the column affected by the constraint

The constraint name syntax is optional, although recommended. If you do not name your constraints, the system will generate constraint names.

Guidelines

You can add, drop, enable, or disable a constraint, but you cannot modify its structure.

You can add a NOT NULL constraint to an existing column by using the MODIFY clause of the ALTER TABLE statement.

Note: You can define a NOT NULL column only if the table is empty or if the column has a value for every row.

Instructor Note

You can defer checking constraints for validity until the end of the transaction.

A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then committing causes the transaction to roll back.

A constraint is immediate if it is checked at the end of each statement. If it is violated, the statement is rolled back immediately.

18 of 30

Adding a Constraint

Add a FOREIGN KEY constraint to the EMPLOYEES

table indicating that a manager must already exist as

a valid employee in the EMPLOYEES table.

ALTER TABLE employees

ADD CONSTRAINT emp_manager_fk

FOREIGN KEY(manager_id)

REFERENCES employees(employee_id);

Table altered.

10-18

19 of 30

Dropping a Constraint

Remove the manager constraint from the EMPLOYEES table.

Remove the PRIMARY KEY constraint on the DEPARTMENTS table and drop the associated FOREIGN KEY constraint on the EMPLOYEES.DEPARTMENT_ID column.

ALTER TABLE employees

DROP CONSTRAINT emp_manager_fk;

Table altered.

ALTER TABLE departments

DROP PRIMARY KEY CASCADE;

Table altered.

10-19

20 of 30

Disabling Constraints

Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint.
Apply the CASCADE option to disable dependent integrity constraints.

ALTER TABLE employees

DISABLE CONSTRAINT emp_emp_id_pk CASCADE;

Table altered.

10-20

21 of 30

Enabling Constraints

Activate an integrity constraint currently disabled in the table definition by using the ENABLE clause. ��
A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY KEY constraint.

ALTER TABLE employees

ENABLE CONSTRAINT emp_emp_id_pk;

Table altered.

10-21

Enabling a Constraint

You can enable a constraint without dropping it or re-creating it by using the ALTER TABLE statement with the ENABLE clause.

Syntax

ALTER TABLE table

ENABLE CONSTRAINT constraint;

In the syntax:

table is the name of the table

constraint is the name of the constraint

Guidelines

If you enable a constraint, that constraint applies to all the data in the table. All the data in the table must fit the constraint.

If you enable a UNIQUE key or PRIMARY KEY constraint, a UNIQUE or PRIMARY KEY index is created automatically.

You can use the ENABLE clause in both the CREATE TABLE statement and the ALTER TABLE statement.

Enabling a primary key constraint that was disabled with the CASCADE option does not enable any foreign keys that are dependent upon the primary key.

Instructor Note

Please read the Instructor Note on page 10-29 for information on the VALIDATE and NOVALIDATE options.

22 of 30

Cascading Constraints

The CASCADE CONSTRAINTS clause is used along with the DROP COLUMN clause.
The CASCADE CONSTRAINTS clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns.
The CASCADE CONSTRAINTS clause also drops all multicolumn constraints defined on the dropped columns.

10-22

23 of 30

Cascading Constraints

Example:

ALTER TABLE test1

DROP (pk) CASCADE CONSTRAINTS;

Table altered.

ALTER TABLE test1

DROP (pk, fk, col1) CASCADE CONSTRAINTS;

Table altered.

10-23

Cascading Constraints (continued)

Submitting the following statement drops column PK, the primary key constraint, the fk_constraint foreign key constraint, and the check constraint, CK1:

� ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;

If all columns referenced by the constraints defined on the dropped columns are also dropped, then CASCADE CONSTRAINTS is not required. For example, assuming that no other referential constraints from other tables refer to column PK, it is valid to submit the following statement without the CASCADE CONSTRAINTS clause:

ALTER TABLE test1 DROP (pk, fk, col1);

Instructor Note

Let the students know that if any constraint is referenced by columns from other tables or remaining columns in the target table, then you must specify CASCADE CONSTRAINTS. Otherwise, the statement aborts and the error ORA-12991: column is referenced in a multicolumn constraint is returned.

24 of 30

Viewing Constraints

Query the USER_CONSTRAINTS table to view all

constraint definitions and names.

SELECT constraint_name, constraint_type,

search_condition

FROM user_constraints

WHERE table_name = 'EMPLOYEES';

…

10-24

Viewing Constraints

After creating a table, you can confirm its existence by issuing a DESCRIBE command. The only constraint that you can verify is the NOT NULL constraint. To view all constraints on your table, query the USER_CONSTRAINTS table.

The example on the slide displays the constraints on the EMPLOYEES table.

Note: Constraints that are not named by the table owner receive the system-assigned constraint name. In constraint type, C stands for CHECK, P for PRIMARY KEY, R for referential integrity, and U for UNIQUE key. Notice that the NOT NULL constraint is really a CHECK constraint.

Instructor Note

Point out to students that the NOT NULL constraint is stored in the data dictionary as a CHECK constraint. Draw their attention to the constraint type, for the NOT NULL constraints in the slide. The entry in the constraint_type field is C (as in CHECK) for these constraints.

25 of 30

Viewing the Columns Associated with Constraints

View the columns associated with the constraint

names in the USER_CONS_COLUMNS view.

SELECT constraint_name, column_name

FROM user_cons_columns

WHERE table_name = 'EMPLOYEES';

…

10-25

26 of 30

Summary

In this lesson, you should have learned how to create

constraints.

Types of constraints:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

You can query the USER_CONSTRAINTS table to view all constraint definitions and names.

10-26

27 of 30

Practice 10 Overview

This practice covers the following topics:

Adding constraints to existing tables
Adding more columns to a table
Displaying information in data dictionary views

10-27

28 of 30

10-28

Practice 10

1. Add a table-level PRIMARY KEY constraint to the EMP table on the ID column. The constraint should be named at creation. Name the constraint my_emp_id_pk.

Hint: The constraint is enabled as soon as the ALTER TABLE command executes

successfully.

2. Create a PRIMARY KEY constraint to the DEPT table using the ID column. The constraint should be named at creation. Name the constraint my_dept_id_pk.

Hint: The constraint is enabled as soon as the ALTER TABLE command executes

successfully.

3. Add a column DEPT_ID to the EMP table. Add a foreign key reference on the EMP table that ensures that the employee is not assigned to a nonexistent department. Name the constraint my_emp_dept_id_fk.

4. Confirm that the constraints were added by querying the USER_CONSTRAINTS view. Note the types and names of the constraints. Save your statement text in a file called lab10_4.sql.�

5. Display the object names and types from the USER_OBJECTS data dictionary view for the EMP and DEPT tables. Notice that the new tables and a new index were created.

If you have time, complete the following exercise:

6. Modify the EMP table. Add a COMMISSION column of NUMBER data type, precision 2, scale 2. Add a constraint to the commission column that ensures that a commission value is greater than zero.

29 of 30

10-29

Instructor Note (for pages 10-21)

You can also set constraints to VALIDATE or NOVALIDATE, in any combination with ENABLE or DISABLE, where:

VALIDATE ensures that existing data conforms to the constraint.

NOVALIDATE means that some existing data may not conform to the constraint.

In addition:

ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed to hold for all rows.

ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint while ensuring that all new or modified rows are valid.

In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on disabled constraints without first validating all data in the table.

DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked and is not necessarily true.

DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns.

Transitions between these states are governed by the following rules:

ENABLE implies VALIDATE, unless NOVALIDATE is specified.

DISABLE implies NOVALIDATE, unless VALIDATE is specified.

VALIDATE and NOVALIDATE do not have any default implications for the ENABLE and DISABLE states.

When a unique or primary key moves from the DISABLE state to the ENABLE state, and there is no existing index, a unique index is automatically created.

Similarly, when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index, the unique index is dropped.

When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data must be checked. (This can be very slow.) However, moving from VALIDATE to NOVALIDATE simply forgets that the data was ever checked.

Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE VALIDATE state does not block reads, writes, or other DDL statements. It can be done in parallel.

The following statements enable novalidate disabled integrity constraints:

ALTER TABLE employees

ENABLE NOVALIDATE CONSTRAINT EMP_EMAIL_UK;

ALTER TABLE employees

ENABLE NOVALIDATE PRIMARY KEY

ENABLE NOVALIDATE UNIQUE (employee_id, last_name);

The following statements enable or validate disabled integrity constraints:

ALTER TABLE employees

MODIFY CONSTRAINT emp_email_uk VALIDATE;

ALTER TABLE employees

MODIFY PRIMARY KEY ENABLE NOVALIDATE;

30 of 30

10-30