SQL Constraints and Types of Constraints

Constraints are used to limit the type of data that can go into a table. Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

You can define integrity constraints to enforce the business rules you want to associate with the information in a database. If any of the results of a DML statement execution violate an integrity constraint, then Oracle rolls back the statement and returns an error.

Example: assume that you define an integrity constraint for the salary column of the employees table. This integrity constraint enforces the rule that no row in this table can contain a numeric value greater than 10,000 in this column. If an INSERT or UPDATE statement attempts to violate this integrity constraint, then Oracle rolls back the statement and returns an information error message.

 A constraint can contain a single column or a group of columns in a table. Oracle allows you to apply the constraints on a single or multiple columns that will enforce data integrity on your table. There are two ways to impose the constraints:

Column level constraints: Column level constraints are just imposing on a single column. The constraints are applied while table creation or modification. Column level constraints cannot be applied if a constraint spans more than one column in a table.

Syntax: (Column_name data type [Constraint <name>] constrain_type, 

Table level constraints: Table level constraints applied on more than one column on a table. These constraints are applied after all the columns of a table defined. These types of constraints are required when you need to impose constraint on more than one column.

Syntax: [CONSTRAINT constraint-name] constrain_type 

Types of Constraints

We will discuss the following constraints:

Not Null Integrity Constraint

By default all columns in a table can contain null values. If you want to ensure that a column must always have a value, i.e. it should not be left blank, then define a NOT NULL constraint on it.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

Syntax: (Column_name [data type] [NOT NULL], 
OR 
(Column_name data type [Constraint <name>] NOT NULL, 

Method 1

Query: The following SQL enforces the “P_Id” column and the “LastName” column to not accept NULL values:

SQL> CREATE TABLE Persons (P_Id number (6) NOT NULL, LastName varchar2 (15) NOT NULL,FirstName varchar2 (15),Address varchar2 (25),City varchar2 (15))

Table created.

Note : In Persons table some persons might have P_Id and some employees might not have any person Id. If you put NOT NULL constraint on P_Id column then you will not be able insert rows for those persons whose P_Id is null.

Method 2

Query: In the following table enforce the Name column not to accept null values.

SQL> CREATE TABLE AA (NAME VARCHAR2(20) CONSTRAINT NN_AA NOT NULL); 

Table Created

UNIQUE key integrity 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 have duplicate values in a specified column or set of columns.

Unique Key constraint is same as primary key i.e. it does not accept duplicate values, except the following differences:

• There can be only one Primary key per table. Whereas, you can have as many Unique Keys per table as you want.

• Primary key does not accept NULL values whereas; unique key columns can be left blank.

It depends on situations; first situation is suppose you have already defined a Primary key constraint on one column and now you have another column which also should not contain any duplicate values, since a table can have only one primary key, you can define Unique Key constraint on these columns.

Second situation is when a column should not contain any duplicate value but it should also be left blank.

Syntax: At column Level: 
(Column_name data type [Constraint <name>] UNIQUE, 
Syntax: At table Level: 
[Constraint <name>] UNIQUE (<column_name>); 

Query: The following SQL enforces the “P_Id” column to take unique values.

SQL> CREATE TABLE Persons ( p_Id number (6) UNIQUE, LastName varchar2 (15), FirstName varchar2 (15), Address varchar2 (25), City varchar2 (15))

Table created.

Query: Table level

SQL> CREATE TABLE Persons( P_Id number (6), LastName varchar2 (15),FirstName varchar2 (15),Address varchar2 (25),City varchar2 (15)Email varchar2(20), constraint UN_persons UNIQUE (email));
Table created.

Query: To define a UNIQUE KEY constraint on an existing table give the following command.

SQL> Alter table v add constraint UN_v unique(roll); OR 
SQL> Alter table v add unique (roll); 

Primary Key Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key. When primary key is applied on a single attribute it is called Simple Key and when it is applied on two or more than two attributes then it is called as Composite Key.

A primary key is a special case of unique keys. The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is. Thus, the values in unique key columns may or may not be NULL. It is a unique identifier, such as a driver license number, telephone number (including area code), or Vehicle identification number (VIN). A relational database must always have one and only one primary key. Primary keys typically appear as columns in relational database tables.

Use the following guidelines when selecting a primary key:

.• Whenever practical, use a column containing a sequence number. It is a simple way to satisfy all the other guidelines.

.• Minimize your use of composite primary keys. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers,

.• Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row the table.

• Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed.

Choose a column that does not contain any nulls. A PRIMARY KEY constraint, by definition, does not allow any row to contain a null in any column that is part of the primary key.

Syntax: At Column level
 
(Column_name data type [Constraint <name>] PRIMARY KEY,
 
Syntax: At table Level:

Query: The following SQL enforces the “P_Id” column to take unique and not null values.

SQL> CREATE TABLE Persons(P_Id number (6) PRIMARY KEY,LastName varchar2 (15),FirstName varcharZ (15),Address varchar2 (25),City varchar2 (15));

Table created.

Query: Table level

SQL> CREATE TABLE Persons(P_Id number (6),LastName varchar2 (15),FirstName varchar2 (15),Address varchar2 (25),City varchar2 (15), CONSTRAINT PK_Persons PRIMARY KEY(P_Id, FirstName));

Table created.

Foreign Key Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. On whichever column you put FOREIGN KEY constraint then the values in that column must refer to existing values in the other table.

A foreign key column can refer to primary key or unique key column of other tables. This Primary key and foreign key relationship is also known as PARENT-CHILD relationship i.e. the table which has Primary Key is known as PARENT table and the table which has foreign key is known as CHILD table. This relationship is also known as REFERENTIAL INTEGRITY.

The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don’t exist in the referenced table (except potentially NULL).

A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table’s primary key values to the other table.

This column becomes a foreign key in the second table.

The foreign key constraint can be imposed on column level or table level. If foreign key constraint uses more than one column then it must be defined on table level.

The following shows an example of parent child relationship:

PARENT TABLE:

SQL> Create table EMP(Empno number (6) primary key, ename varchar2 (15), salary number (6), Comm number (5), deptno number (5), idno number (6));

Table created

CHILD TABLE:

SQL> create table attendance(Month varchar2 (9), days number (5), Empno number (6) constraint fk_attendance references EMP (empno));

Table created.

FOR TABLE LEVEL:

SQL>Create table attendance (month varchar2(7). days number(6). empno number(6), constraint fk_attendance foreign key(empno) references EMP (empno));

Table created.

Here EMPNO in attendance table is a foreign key referring to EMPNO of EMP table.

Some points to remember for referential integrity:

• You cannot delete a parent record if any existing child record is there. If you have to first delete the child record before deleting the parent record. In the above example you cannot delete row of employee no. 101 since its child exist in the ATTENDANCE table. However, you can delete the row of employee no. 103 since no child record exists for this employee in ATTENDANCE table. If you define the FOREIGN KEY with ON DELETE CASCADE option then you can delete the parent record and if any child record exists it will be automatically deleted.

• A composite foreign key must reference a composite primary key with the same number of columns and data types .

• The number of columns for the foreign key must match the:;number of columns of primary key.

Check Constraint

Check constraint is used to validate values entered into a column. 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.

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 that ranges from 15,000 through 500, 00. This prevents salaries from being entered beyond 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.

Syntax:

CONSTRAINT constrain_name CHECK (predicate),

COLUMN LEVEL

SQL> CREATE TABLE Persons(P_Id number (6) NOT NULL CHECK (P_Id>0),LastName varchar2 (15) NOT NULL,FirstName varchar2 (15),Address varchar2 (25),City varchar (15) check (city in (‘Ferozepur’,‘Jalandhar’,‘Nabha’)));

Table created.

TABLE LEVEL

SQL> CREATE TABLE Persons(P_Id number (6) NOT NULL,LastName varchar2 (15) NOT NULL,FirstName varchar2 (15),Address varchar2(25),City varchar2 (13),CONSTRAINT ck_Person CHECK (P_Id>0 AND City=‘Ferozepur’));

TIPS:

• You can use any number of constraints in a table.

• A single column can have any number of check constraints.

Default Constraint

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. You can also specify the DEFAULT value for columns i.e. when user does not enter anything in that column then that column will have the default value. Default constraints are ANSI -standard constraints you can assign to a column either with the CREATE TABLE or ALTER TABLE statements.

Example: in Persons table suppose most of the Persons are from Ferozepur, then you can put this as default value for CITY column. Then while inserting records if user doesn’t enter anything in the CITY column then the city column will have Ferozepur.

Syntax:

<column_name><datatype> [default <expression>]
Query:

SQL> CREATE TABLE Persons(P_Id number (6) NOT NULL CHECK (P_Id>0),LastName varchar2 (15) NOT NULL,FirstName varchar2 (15),Address varchar2 (25),City varchar (15) DEFAULT ‘Ferozepur’);Table created.

Now, when user inserts record like this

SQL> insert into Persons values (301,'Kumar' ,'Vinod' ,'VPO Rukna Mungla',' Jalandhar'); 

Then the city column will have value ‘Jalandhar ‘. But when user inserts a record like this.

SQL> insert into Persons values (302,'Dhillon','Raman','Preet nagar'); 

Then the city column will have value ‘Ferozepur’. Since it is the default.