Blog

SQL Server Constraints with Example

  • (4.0)
  • | 370 Ratings

Introduction

  • Constraints are used to prevent invalid data entry into our tables.
  • Generally constraints are created on table columns.

Oracle server having following types of contraints –

1. Not null.
2. Unique
3. Primary Key
4. Foreign Key
5. Check

In all Databases all above constraints are defined into two ways

(i) Column level
(ii) Table level

Learn how to use SQL Server, from beginner basics to advanced techniques. Enroll for Free SQL Server Training Demo!

(i) Column level:- 

In this method we are defining constraints in individual columns i.e. whenever we are defining column than only we are specifying constraints type.

Syntax:-

Create table tablename (col1 datatype (size) constraints type, col2 datatype (size) constraints type, _ _ _  )

(ii) Table level:-

In this method we are defining constraints on group of columns i.e. first we specifying all columns and last only we’re specifying constriants type, alsong with group of columns.

Syntax:-

Create table tablename (col1 datatype (size), col2 datatype (size), _ _ _ _ constraint type (col1, col2, ….) );

1. NOT NULL:-

  • In all databases NOT NULL doesn’t support table level.
  • NOT NULL doesn’t accept Null value but it accepts duplicate values.

Column level:-

Eg :-

SQl > create table z1 (sno number (10) not null, name varchar2);
SQL > insert table z1 values (null, ‘a’);
Error : cannot insert Null into sno
SQL > insert table z1 values (1, ‘X’);
SQL > insert table z1 values (1, ‘y’);

O/P:  

S.No Name
1 X
1 Y

2. Unique:-

  • In all databases unique constraints defined on column level, table level.
  • Unique constraints doesnot accept duplicate value but accept Null values.

Note:- Whenever we are creating unique constrains internally oracle server automatically creates B-free indexes for those columns.

Column level:-

SQL > create table z2 (sno number (10) unique, name varchar2(10));

Table level:-

SQL > create table z3 (sno number (10), name varchar2(10), unique (sno, name));
SQL > select * from z3;

O/P:  

S.No Name
1 Sarika
1 abc

SQL > insert into z3 values (1, ‘abc’)

Error : unique constraints violated

Check Out SQL Server Tutorials

3. Primary Key:-

  • Primary key uniquely identifying a record in a table, in all databases their can be only one primary key in a table and also primary key doesn’t accept duplicate values, null values.
  • Whenever we are creating B-free indexes on those columns.

Column Level:-

SQL > create table z4 (Sno number (10) primary key, name vaschar2(10));

Table Level:-

SQL > create table z5 (Sno number (!0), name varchar2(10), Primary key (sno, name));

This is also called as composite primary key i.e. it is a combination of column as a single primary key.

4. Foreign Key:-

  • In all databases if you want to establishes relationship between tables then we are using referencial integrity constraints foreign key.
  • One table foreign key must belong to another table primary key and also these two columns must belongs to same datatype.
  • Always foreign key values always based on primary key values only.
  • Generally primary key doesn’t accepts duplicate, Null values where as foreign key accepts duplicate, null values.

Column level (References):-

Syntax:-

SQL > create table tablename (col1 datatype (size) references master tablename (primary key col name));

SQL > create table w4 (sno number (!0) references z4);

Table Level:- (Foreign key, references)

Syntax:-

create table tablename (col1 datatype (size), col2 datatype (size), …..
                         Foreign key (col1, col2,…)
                         References
                         Mastertablename(primary key colnames));

Eg:-

SQL > create table z6 (sno number (10), name varchar2(10); Foreign key (sno, name) references z5);

Whenever we are establishing relationship between table by using Foreign key then oracle server automatically violet following two rules; these are :-

A. Deletion in master table.
B. Insertion in child table.

A. Deletion in master table

When we are trying to delete a master table record in master table if the record exists in child table then oracle server returns an error ora – 2292. To overcome this problem if we want to delete master table record in master table then first we must to delete child table record in child table then only we are allow to delete those record in master table otherwise use an on delete cascade clause.

On delete cascade:-

This clause is used along with foreign key only. Whenever we are specifying this clause in child table then we are deleting a master table record within master table then automatically the record is deleted in master table and those records are automatically deleted in child table.

Syntax:-

Create table tablename (col1 datatype(size) references mastertable name (primary key col name) on delete cascade, ……);

TESTING (DELETION IN MASTER TABLE)

SQL > delete from mas where Sno=1;

One row deleted

SQL > select * from mas;

O/P: 

S.No
2
3

SQL > select * from child;

O/P:

S.No
2
2
3

On delete set null:-

Oracle also supports another clause along with foreign key on delete set null. Whenever we use this clause, whenever we are deleting primary key value in master table then automatically that record is deleted in master table and corresponding foreign key values are automatically set to null in child table.

Syntax:-

Create table tablename (col1 datatype (size) references mastertablename (primary key colname) on delete set null…… );

B. Insertion in child table:-

When we are try to insert otherthan primary key values into foreign key then oracle server returns an error ora-2291 because in all databases always foreign key values based on primary key values only.

Note:-

Generally when we are truncating master table by using “truncate table tablename” than DB servers returns error to overcome this problem oracle 12C introduced. Cascade clause along with truncate table tablename.

Syntax:-

Truncate table mastertablename cascade;
Before we are using this command we must use on delete cascade class along with foreign key.

Frequently Asked SQL Server Interview Questions

5. Check:-

  • Check constrains are used to define logical condition according to client business rules.
  • In oracle check constraints doesnot work with sys Date Function.

Column Level:-

Syntax-

SQL > Create table tablename (col1 datatype ( size) check (logical condition) col2 datatype (size), …..);

Eg -1:-  

    SQL > create table test (sal number (10) check (sal > 2000));
    SQL > insert into test values (1000);
                           Error:
    SQL > insert into test values (5000);
               1 row created
     SQL > select * from test;

SAL
5000

Ex – 2:-

     SQL > create table test1 (name vachar2(10) check (name = upper (name)));
     SQL > insert into test1 values (‘abc’);
                     Error:
     SQL > insert into test1 values (‘ABC’);
               1 row created
     SQL > select * from test1;

O/P: 

NAME
ABC

Table level:-

Syntax:-

Create table tablename (colname, datatype (size), colname2 datatype (size) check (cond1, cond2, ….));

Eg:-

SQL > create table test1 (name varchar2(10), sal number (10), check (sal > 2000 and name = upper (name)));

Assign user defined names to constraints:-
(OR)
Constraints Names:

In all DB whenever we are creating constraints then DB server automatically converts on unique identification no for identifying a constraints auniquely, oracle server also automatically generates an unique identification number in the format of sys_cn  This is called Predefined constraint name in place any number of this one we can also create our own name by using constraints keyword this is called userdefined constraint name.
 
Syntax:-
 
Ex:-        Predefined constraint name:-
              SQL > create table test (sno number (10) primary key);
              SQL > insert into test values (1);   
              SQL > insert into test values (1);    X
 

Error: Unique constraints

(SCOTT_SYS (005571)) violated.

Ex:-  User defined constraint name:-

SQL > create table test1 (sno number (10) constraint P_sno primary key);

SQL > insert into test1 values (1); 

SQL > insert into test1 values (1);

    Error: Unique constraints

                       (SCOTT_P.SNO) violated.

  User.defined constraint name.

List of Related Microsoft Certification Courses:

 SSRS  Power BI
 SSAS  SQL Server
 SCCM  SQL Server DBA
 SharePoint  BizTalk Server
 Team Foundation Server  BizTalk Server Administrator