Self Referential Integrity Constraint in SQL – SQL Server Tutorial

SELF REFERNTIAL INTEGRITY  :-

A foreign key in one table refers primery key of same table then it is called “self referential integrity” and also called “Unary relationship”

  • Empno                     ename                    manager
  • 1                                 a                           —
  • 2                                 b                           1
  • 3                                 c                            1                          
  • 4                                 d                            2                                    
  • 5                                 e                             10

The above table consists primary key -> Empno and Foreign Key -> Manager

 {here,Foreign Key refers to primary key in the same table}

Example   :-  Create table emp33

(eno int primery key, Ename varchar(20), Manager int referencies emp33(eno))

(ii) TABLE LEVEL   :

  • The table level constraint are declared after declaring all the columns.
  • Use table level when multiple column participates in constraints declaration.

Syntax    :-     Create table <tabname>

 (colname data type (size), Colname data  type (size), [constraint<name>] type (collist),–).

  • Not-null can not be declared in table level.

Example   :–

Create table stududent   (sid int primery key, sname varchar (20) Not null)

Create table course (cid int primery key, Cname varchar(20) not null).

Create table student_course

(sid int references stud(sid), cid int references course(cid), doc date time, primery key (sid,cid))

create table certificates

(cent no int primery key, Doi date time, Sid int, cid int, constraint fk_sid_cid foreign key (sid,cid) references stud_course(sid,cid))

Cust                                                                Items
cid      cname                                                icode           dept       rate

Order                                                            Order-details
Ordno  ord date   custid                        ordno     icode        qty

Leave a Reply

Your email address will not be published. Required fields are marked *