SQL Server Constraints with Example

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.

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.