Oracle database -12c -4

Constraint:

1. Types of constraints
2. Sample tables
3. Creating integrity constraints
4. Creating example table

WHAT IS A CONSTRAINT? In the previous chapter we have seen how to create a table using CREATE TABLE command. Now we will understand how to define constraints. Constraints are used to implement standard and business rules. Data integrity of the database must be maintained. In order to ensure data has integrity we have to implement certain rules or constraints. As these constraints are used to maintain integrity they are called as integrity constraints.

Standard rules Standard constraints are the rules related to primary key and foreign key. Every table must have a primary key. Primary key must be unique and not null. Foreign key must derive its values from corresponding parent key. These rules are universal and are called as standard rules.

Business rules These rules are related to a single application. For example, in a payroll application we may have to implement a rule that prevents any row of an employee if salary of the employee is less than 2000. Another example is current balance of a bank account must be greater than or equal to 500.

Once the constraints are created, Oracle database makes sure that the constraints are not violated when a row is inserted, deleted or updated. If constraint is not satisfied then the operation will fail.

Constraints are normally defined at the time of creating table. But it is also possible to add constraints after the table is created using ALTER TABLE command. Constraints are stored in the Data Dictionary (a set of tables which stores information regarding database).

Each constraint has a name; it is either given by user using CONSTRAINT option or assigned by system. In the later case, the name is SYS_Cn; where n is a number.

Note: It is recommended that you use constraint name so that referring to constraint will be easier later on.

TYPES OF CONSTRAINTS Constraints can be given at two different levels. If the constraint is related to a single column the constraint is given at the column level otherwise constraint is to be given at the table level. Based on where a constraint is given, constraints are of two types:

o Column Constraints

o Table Constraints

Column Constraint A constraint given at the column level is called as Column Constraint. It defines a rule for a single column. It cannot refer to column other than the column at which it is defined. A typical example is PRIMARY KEY constraint when a single column is the primary key of the table.

Table Constraint A constraint given at the table level is called as Table Constraint. It may refer to more than one column of the table. A typical example is PRIMARY KEY constraint that is used to define composite primary key. A column level constraint can be given even at the table level, but a constraint that deals with more than one column must be given only at the table level.

SQL CREATE TABLE with CONSTRAINT Syntax CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... );

The following is the syntax of CONSTRAINT clause used with CREATE TABLE and ALTER TABLE commands.

[CONSTRAINT constraint] { [NOT] NULL | {UNIQUE | PRIMARY KEY} | REFERENCES [schema.] table [(column)] [ON DELETE CASCADE] | CHECK (condition) }

The following is the syntax of table constraint.

[CONSTRAINT constraint] { {UNIQUE | PRIMARY KEY} (column [,column] ...) | FOREIGN KEY (column [,column] ...) REFERENCES [schema.] table [(column [,column] ...)] [ON DELETE CASCADE] | CHECK (condition) }

The main difference between column constraint and table constraint is that in table constraint we can access more than one column whereas in column constraint we can refer to only the column for which constraint is being defined.

NOT NULL - Indicates that a column cannot store NULL value

CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

UNIQUE - Ensures that each row for a column must have a unique value

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly

FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table

CHECK - Ensures that the value in a column meets a specific condition

DEFAULT - Specifies a default value for a column