Relational Query Languages | INTEGRITY CONSTRAINT IN SQL

INTEGRITY CONSTRAINT IN SQL

CONSTRAINTS- Constraints are used to prevent the valid values in the table.

Several Types of Constraints-

1. Not null

2. Unique

3. Primary key constraint

4. Check constraint

5. Foreign key

 

1. NOT NULL- NOT NULL constraints in Microsoft SQL Server allow you to specify that a   column may not contain NULL values.

SYNTAX- create table <table name> (column name1 datatype(size) constraint <constraint name> not null, column name2 datatype(size));

EG-  create  table  mydoc1 ( username varchar (20) constraint  name_nn  not null ,password  number(10));

2. UNIQUE- You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. 

SYNTAX- create table <table name> (column name1 datatype(size) , column name2 datatype(size) unique);

EG- create  table  mydoc2 ( username varchar (20) ,password  number(10) unique ); 

3. PRIMARY KEY CONSTRAINT-

It is the mixture of unique and no null constraint. The primary key of a relational table uniquely identifies each record in the table. 

SYNTAX- create table <table name> (column name1 datatype (size), column name2 datatype(size) constraint  rollno_pk  primary key);

EG- create  table  mydoc3 ( username varchar (20) ,password  number(10) constraint  rollno_pk  primary key);

4.CHECK CONSTRAINT-

A check constraint is applied to each row in the table.[clarification needed] The constraint must be a predicate. It can refer to a single or multiple columns of the table. The result of the predicate can be either  TRUE, FALSE, or UNKNOWN, depending on the presence of NULLs. If the predicate evaluates to UNKNOWN, then the constraint is not violated and the row can be inserted or updated in the table. This is contrary to predicates in WHERE clauses in SELECT or UPDATE statements.

SYNTAX- create table <table name> (column name1 datatype(size)  , column name2 datatype(size) , column name3 datatype(size) check (condition);

EG- create table student (sname varchar2(20)  , rollno number(12) , attendance integer check (attendance between 0 and 100));

4.FOREIGN KEY-

A foreign key is a field in a relational table that matches a candidate key of another table. The foreign key can be used to cross-reference tables. 

SYNTAX- create table <table name> (column name1 datatype (size), column name2 datatype(size) constraint  rollno_pk  primary key);

create table <table name> (column name1 datatype (size), column name2 datatype(size) references <tablename which contain primary key < primary key attribute>);

 EG- create  table  doc9    (bname  varchar(20),brollno number(20)    constraint vn_o   primary key); 

create  table  doc10   (sname  varchar(20),srollno number(20)    references  doc9(brollno));