SQL Constraint

SQL Constraint

Constraint:-Constraints are set of rules that are applied on column/columns.

The Constraints always enforce the rules and prevent invalid data in a table.
If Constraint not satisfy it’ll be violated, that means if Constraint condition does not satisfy for the DML operations then operations will not permitted.

Type of Constraints:-       1) Primary Key
                                         2) Unique Key
                                         3) Check Constraint
                                         4) Not Null
                                         5) Foreign Key

Primary Key:- Primary key is a combination of NOT NULL and UNIQUE Key Constraint, Primary key never allow duplicate values and null values in the column.

Unique Key: – Unique Key does not allow duplicate value but it accept NULL value many times (As every null is unique and never equal to another null).

Check Constraint: – Check constraint check the condition which is specify on the column that must be TRUE.

Not Null Constraint: – NOT NULL Constraint never allow null values in column.

Foreign Key: – Foreign Key is a key in a table which is already defined as a primary/unique key in another/same table . Foreign Key create  Parent-Child relationship. It accept values of reference column from master table or null values
             
Note: – Constraint can be defined 2 levels 1) Column Level     2) Table Level

Composite Constraints :- If any constraint defined for more than one column then it called composite constraint.
                         Composite constraint can be defined in table level definition only.


Note :- Constraint can be created at the time of table creation or by alter command (After table creation).

Examples :- At the Table Creation

Primary Key (Column Level) :-
With System defined name
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course varchar2(10));

With User defined name
SQL>create table student(regno number(10) constraint pk_regno primary key,name varchar2(10),dob date,course varchar2(10));

Primary Key (Table Level) :-
With System defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10), primary key(regno));

With User defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10),constraint pk_regno primary key(regno));

Composite Primary Key :- Key, that is created for more that one column is called composite key. If Primary Key is created for more than one column then that Primary Key is called Composite Primary Key.

With System defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10), primary key(regno,name));

With User defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10),constraint pk_regno primary key(regno,name));
                  
                  
Unique Key (Column Level) :-
With System defined name

SQL>create table student(regno number(10) unique, name varchar2(10),dob date,course varchar2(10));

With User defined name

SQL>create table student(regno number(10) constraint uk_regno unique,
                     name varchar2(10),dob date,course varchar2(10));

Unique Key (Table Level) :-
With System defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10), unique(regno));

With User defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10),constraint uk_regno unique(regno));

Composite Unique Key :- Unique Key created for more than one column is called Composite Unique Key.

With System defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10), unique(regno,name));

With User defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10),constraint uk_regno unique(regno,name));                  
                  

Check Constraint (Column Level) :-
With System defined name
SQL>create table student(regno number(10), name varchar2(10),dob date,course varchar2(10) check (course in ('MCA','BCA')));

With User defined name
SQL>create table student(regno number(10), name varchar2(10),dob date,course varchar2(10) constraint ck_course check (course in ('MCA','BCA')));

Check Constraint (Table Level) :-
With System defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10), check (course in ('MCA','BCA')));

With User defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10),constraint ck_course check (course in ('MCA','BCA')));

Composite Check Constraint :-Check constraint defined for more than one column is called Composite Check Constraint.

With System defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10),check (course in ('MCA','BCA') and regno>1000));

With User defined name
SQL>create table student(regno number(10),name varchar2(10),dob date,course varchar2(10), constraint ck_regno check (course in ('MCA','BCA') and regno>1000));
                  
Not Null Constraint :- (Only Column Level definition is possible)                  

With System defined name
SQL>create table student(regno number(10), name varchar2(10) not null,dob date,course varchar2(10) not null));

With User defined name
SQL>create table student(regno number(10), name varchar2(10) constraint nn_name not null,dob date,course varchar2(10));

Foreign Key (Column Level) :-
With System defined name
SQL>create table courses (course_id number primary key,course_name varchar2(20));
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number
                     references courses(course_id));

With User defined name
SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));
SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number constraint fk_course_id references courses(course_id));

Foreign Key (Table Level) :-
With System defined name
SQL>create table courses (course_id number primary key,course_name varchar2(20));
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number, foreign key (course_id) references courses(course_id));

With User defined name
SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));
SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number, constraint fk_course_id foreign key(course_id) references courses(course_id));

Composite Foreign Key :-
With System defined name
SQL>create table courses (course_id number,course_name varchar2(20),primary key (course_id,course_name));
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number, foreign key (course_id) references courses(course_id));

With User defined name
SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));
SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number, constraint fk_course_id foreign key(course_id) references courses(course_id));


On delete set null :-
 
With System defined name
SQL>create table courses (course_id number primary key,course_name varchar2(20));
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number,foreign key (course_id) references courses(course_id) on delete set null);
                  
With User defined name
SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));
SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number, constraint fk_course_id foreign key(course_id) references courses(course_id) on delete set null);


With System defined name
SQL>create table courses (course_id number,course_name varchar2(20),primary key (course_id,course_name));
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number,course_name varchar2(20),
                     foreign key (course_id,course_name) references courses(course_id,course_name) on delete set null);

With User defined name
SQL>create table courses (course_id number,course_name varchar2(20),constraint pk_course_id_name primary key (course_id,course_name));
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number,course_name varchar2(20),
                     constraint fk_course_id_name foreign key (course_id,course_name) references courses(course_id,course_name) on delete set null);
                    
On delete cascade :-
 
With System defined name
SQL>create table courses (course_id number primary key,course_name varchar2(20));
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number, foreign key (course_id) references courses(course_id) on delete cascade);
                  
With User defined name
SQL>create table courses (course_id number constraint pk_course_id primary key,course_name varchar2(20));
SQL>create table student (regno number(10) constraint pk_regno primary key, name varchar2(10),dob date,course_id number,constraint fk_course_id foreign key(course_id) references courses(course_id) on delete cascade);

Composite Foreign Key :-
With System defined name
SQL>create table courses (course_id number,course_name varchar2(20),primary key (course_id,course_name));
SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number,course_name varchar2(20),
                     foreign key (course_id,course_name) references courses(course_id,course_name) on delete cascade);

With User defined name
SQL>create table courses (course_id number,course_name varchar2(20),constraint pk_course_id_name primary key (course_id,course_name));

SQL>create table student(regno number(10) primary key, name varchar2(10),dob date,course_id number,course_name varchar2(20),
                     constraint fk_course_id_name foreign key (course_id,course_name) references courses(course_id,course_name) on delete cascade);