Constraints in Database

• Constraint in a database is a mechanism used by Oracle (or) SQL Server to control the unwanted or invalid data flow into a table. (or)
• Enforcing the rules to maintain the data integrity.
• This mechanism automatically activates when user performance a DML operation.

Types of Constraints:

1. NOT NULL: It will not allows the NULL values, but allow the duplicate values.
2. UNIQUE: It will now allows the duplicate values, but allows NULL values.
3. Primary Key:
1. It is a combination of unique, not null and clustered index.
2. It will not allow NULL values and duplicated values.

     4. Default: It is used to insert default values into the column when user skips the column from insert statement.

     5. Check: It is used to impose a condition rule.

     6. Foreign Key:

1. It is used to establish parent/child or Master/Detailed relationships between the tables.
2. It always linked to primary key column or unique key column of master table.
3. It will not allow NULL values.

Constraints can be enforced into tables in two levels.

1. Column level constraints
2. Table level constraints

1. Column level Constraints:

create table dept 
(deptno int primary key,
 dname varchar2(20) not null unique,
 loc varchar2(20) dafault 'HYDERABAD')

By issuing the below query one record is inserted without any issues.

insert into dept values (10,'Accounting','New York')

By issuing the below query error will be displayed like due to violation of primary key constraints.

insert into dept values (10,'Research','Dallas')

By issuing the below query error will be displayed like due to NULL values not allowed for deptno column

insert into dept values (NULL,'Research','Dallas')

By issuing the below query error will be displayed like due to violation of unique constraints.

insert into dept values (20,'Accounting','Dallas')

Activating default constraints:

insert into dept values (30, ‘Sales’, default)

insert into dept (deptno, dname) values (40, ‘Operations’)

Displaying Name of the Constraint in SQL Server:

SP_HELPCONSTRAINT TABLE_NAME

Displaying Name of the Constraints in Oracle:

We will be having so many dictionary table on Oracle, we can find constraint from below.

Select * from all_tab_constraints where table_name=’dept’

’

Creating emp table with constraints:

Create table emp 
 (empno integer primary key,
ename varchar(10) not null,
 Job varchar(10),
Mgr int,
Hiredate datetime default getdate(),
Sal int check (sal>=1000),
Comm int,
Deptno int references dept(deptno) on delete cascade) 

Advantage of on delete cascade:

• If on delete cascade key work not used in child table (emp as dept is parent table), it is not possible to delete the records from parent table if there are any dependent records in the child table.
• When on delete cascade keyword is used if the record in the master table is deleted and corresponding dependent records in child table also deleted.

Assigning the user name for the constraints:

If you provide the constraint name it will give us idea of constraint details like which table the constraint got created and on which columns etc. Otherwise simply system will assign the constraint with prefix SP_ in SQL Server and SYS_ in Oracle. The only difference in Oracle and SQL Server is datatypes.

In SQL Server
Create table test ( tno integer constraint test_tno_pk primary key)

In Oracle
Create table test ( tno number(10) constraint test_tno_pk primary key)

2. Table Level Constraints:

• Constraints are added after declaring all the columns or constraints are added for the existing table.
• “Default” and “NOT NULL” constraints are not allowed
• More than one column of a table can be declared as primary key or foreign key columns.
• In column level constraints only one column can be declared as either foriegn key column or primary key column.

create table dept (deptno int, dname varchar(20),
loc varchar(10), primary key (deptno), unique(dname))

create table emp (empno int, ename varchar(20),
job varchar(10), mgr int, hiredate datetime, comm int,
deptno int,primary key (empno),check (sal >1000),
foreign key (deptno),
references dept (deptno) on delete cascade)

Adding constraints for existing table:-

Create emp and dept tables without having the constraints.

Syntax:
ALTER TABLE ADD CONSTRAINT_TYPE (COLUMN_NAME)

Adding constraints  for existing table

alter table dept alter column deptno int not null

alter table emp alter column empno int not null

alter table dept add unique (dname)

alter table emp add primary key (empno)

alter table emp add foreign key (deptno) references dept (deptno)
on delete cascade

Assigning username for the constraints in the table level:

alter table  
add constraint 
constraint type (column)

alter table dept 
add constraint dept_deptno_pk
primary key (deptno)

Declaring more than one column as primary key or foreign key:

create table bank(account_no int,
    name varchar(20),
    acct_type char(1),
    open_balance int,
    open_date datetime,
    primary key (account_no,name))

Self referential integrity:
The foreign key column of a table is referring to the primary key column of a same table.

create table emp_self
empno int,ename varchar,
mgr int, primary key (empno),
foreign key (mgr) references emp_self(empno))