Constraints are a set of predefined rules, which ensure that the valid data values are stored in the columns of a table. Oracle provides some predefined commands that enable you to define the constraints for a table or a column. There are two types of constraints: integrity and value. The integrity constraints include primary key and foreign key. The value constraints define specific data values or data ranges. The values entered in columns should not be Null. There are two levels of constraints: table level constraint and column level constraint.
The table level constraints restrict the values that a table can store. These constraints can be referred to one or more columns in a table. The table level constraint includes the following constraints: PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK.
The column level constraints can be referred to a single column in a table, and they do not specify a column name, except the CHECK constraint. As a result, they limit the values that can be placed in a specific column, irrespective of values that exist in other table rows. The column level constraint can be one of the following: UNIQUE, NOT NULL, PRIMARY KEY, and FOREIGN KEY.
The syntax and behavior of the table level constraint and the column level constraint is similar with only the following difference:
CONSTRAINT [Constraint_Name] Constraint_Type
Primary Key Constraint
The primary key constraints ensure that the Null values are not entered in a column and also the value entered is unique. Thus, these constraints avoid the duplication of records. A primary key constraint can be defined in the CREATE TABLE and ALTER TABLE commands. This constraint can be declared at both levels: within the column level and at the table level.
The syntax for declaring a primary key constraint at the column level is as follows:
CONSTRAINT Constraint_Name PRIMARY KEY
The syntax for declaring a primary key constraint at the table level is as follows:
CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name)
You can also create a primary key constraint for more than one column. The syntax for declaring the primary key for more than one column is as follows:
CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name1,
Column_Name2, Column_Name3, Column_Name4 ...)
Foreign Key Constraint
The foreign key constraint is the property that guarantees the dependency of data values of one column of a table with another column of a table. A foreign key constraint, also known as referential integrity constraint, is declared for a column to ensure that the value in one column is found in the column of another table with the primary key constraint. The table containing the foreign key constraint is referred to as the child table, whereas the table containing the referenced (Primary key) is referred to as the parent table. The foreign key reference will be created only when a table with the primary key column already exists. The foreign key constraint can be declared in two ways: within the column declaration and at the end of the column declaration.
The syntax for using the foreign key constraint within the column declaration is as follows:
CONSTRAINT Constraint_Name REFERENCE Primary_Key_Table_Name
(Primary_Key_Column_Name)
The syntax for declaring the foreign key constraint at the end of the column declaration:
CONSTRAINT Constraint_Name FOREIGN KEY (Column_Name) REFERENCE
Primary_Key_Table_Name (Primary_Key_Column_Name)
NOT NULL Constraint
A column in a table can be declared with the NOT NULL constraint. On declaring this constraint, you cannot insert Null value in the column. You can add this constraint while creating the table by using the CREATE TABLE command. You can also add this constraint after creating the table by using the ALTER command. The ALTER command will be discussed later in the chapter.
The syntax for declaring the NOT NULL constraint within the column declaration is as follows:
CONSTRAINT Constraint_Name NOT NULL
CHECK Constraint
The CHECK constraint ensures that all values inserted into the column satisfy the specified condition. This constraint checks data against the expression defined in the INSERT and UPDATE statement. The CHECK constraint can be declared at the column level.
The syntax for declaring the CHECK constraint within the column declaration is as follows:
CONSTRAINT Constraint_Name CHECK(Col_Condition)
UNIQUE Key Constraint
The UNIQUE key constraint is used to prevent the duplication of data values within the rows of a specified column or a set of columns in a table. The column defined with the UNIQUE key constraint can also allow a null value. Moreover, this constraint can be added to the existing columns. The UNIQUE key constraint can be declared both at the column level and the table level.
The syntax for declaring the UNIQUE key constraint at the column level is as follows:
CONSTRAINT Constraint_Name UNIQUE
DEFAULT Constraint
The DEFAULT constraint is used to set the default value for a column. This constraint ensures that a default value is set automatically by Oracle for each column of a table. The DEFAULT
constraints are declared at the column level declaration.
The syntax for declaring the DEFAULT constraint is as follows:
DEFAULT ‘default_value’
Example:
Country VARCHAR2 (50) DEFAULT ‘USA’
The table level constraints restrict the values that a table can store. These constraints can be referred to one or more columns in a table. The table level constraint includes the following constraints: PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK.
The column level constraints can be referred to a single column in a table, and they do not specify a column name, except the CHECK constraint. As a result, they limit the values that can be placed in a specific column, irrespective of values that exist in other table rows. The column level constraint can be one of the following: UNIQUE, NOT NULL, PRIMARY KEY, and FOREIGN KEY.
The syntax and behavior of the table level constraint and the column level constraint is similar with only the following difference:
- The syntax for table level constraints is separated from the column definitions by comma.
- The table level constraints must follow the definition of the columns to which they are referred.
- The table level constraint can be defined for more than one column and SQL evaluates the constraint based on the combination of values stored in all columns.
CONSTRAINT [Constraint_Name] Constraint_Type
Primary Key Constraint
The primary key constraints ensure that the Null values are not entered in a column and also the value entered is unique. Thus, these constraints avoid the duplication of records. A primary key constraint can be defined in the CREATE TABLE and ALTER TABLE commands. This constraint can be declared at both levels: within the column level and at the table level.
The syntax for declaring a primary key constraint at the column level is as follows:
CONSTRAINT Constraint_Name PRIMARY KEY
The syntax for declaring a primary key constraint at the table level is as follows:
CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name)
You can also create a primary key constraint for more than one column. The syntax for declaring the primary key for more than one column is as follows:
CONSTRAINT Constraint_Name PRIMARY KEY (Column_Name1,
Column_Name2, Column_Name3, Column_Name4 ...)
Foreign Key Constraint
The foreign key constraint is the property that guarantees the dependency of data values of one column of a table with another column of a table. A foreign key constraint, also known as referential integrity constraint, is declared for a column to ensure that the value in one column is found in the column of another table with the primary key constraint. The table containing the foreign key constraint is referred to as the child table, whereas the table containing the referenced (Primary key) is referred to as the parent table. The foreign key reference will be created only when a table with the primary key column already exists. The foreign key constraint can be declared in two ways: within the column declaration and at the end of the column declaration.
The syntax for using the foreign key constraint within the column declaration is as follows:
CONSTRAINT Constraint_Name REFERENCE Primary_Key_Table_Name
(Primary_Key_Column_Name)
The syntax for declaring the foreign key constraint at the end of the column declaration:
CONSTRAINT Constraint_Name FOREIGN KEY (Column_Name) REFERENCE
Primary_Key_Table_Name (Primary_Key_Column_Name)
NOT NULL Constraint
A column in a table can be declared with the NOT NULL constraint. On declaring this constraint, you cannot insert Null value in the column. You can add this constraint while creating the table by using the CREATE TABLE command. You can also add this constraint after creating the table by using the ALTER command. The ALTER command will be discussed later in the chapter.
The syntax for declaring the NOT NULL constraint within the column declaration is as follows:
CONSTRAINT Constraint_Name NOT NULL
CHECK Constraint
The CHECK constraint ensures that all values inserted into the column satisfy the specified condition. This constraint checks data against the expression defined in the INSERT and UPDATE statement. The CHECK constraint can be declared at the column level.
The syntax for declaring the CHECK constraint within the column declaration is as follows:
CONSTRAINT Constraint_Name CHECK(Col_Condition)
UNIQUE Key Constraint
The UNIQUE key constraint is used to prevent the duplication of data values within the rows of a specified column or a set of columns in a table. The column defined with the UNIQUE key constraint can also allow a null value. Moreover, this constraint can be added to the existing columns. The UNIQUE key constraint can be declared both at the column level and the table level.
The syntax for declaring the UNIQUE key constraint at the column level is as follows:
CONSTRAINT Constraint_Name UNIQUE
DEFAULT Constraint
The DEFAULT constraint is used to set the default value for a column. This constraint ensures that a default value is set automatically by Oracle for each column of a table. The DEFAULT
constraints are declared at the column level declaration.
The syntax for declaring the DEFAULT constraint is as follows:
DEFAULT ‘default_value’
Example:
Country VARCHAR2 (50) DEFAULT ‘USA’
