Cascade Constraint in MsSqlServer

Constraints on Existing Table Columns

  • –How Can Delete Data From Parent as Well as From Child Table
  • /*for this Purpose we have Four Methods
  • 1)    no action:- If will try to delete data from master table then foreign key will not permission to u Delete
  • 2)    cascade:- Data Delete from parent as well as child table
  • 3)    set default :- Data delete from parent table and default value set into secondary table on place of fk
  • 4)    set null:- Data delete from parent table and null value set into secondary table on place of fk
  • */
  • alter table tbemp drop constraint fk_dno
  • –Now We Can Delete Data From Master table but we will have to delete from Child Table
  • exec sp_helpconstraint tbemp
  • select *From tbemp
  • –Casecade Method and Foreign Key Constraint Method of Implementation
  • alter table tbemp add constraint fk_dno foreign key(edno) references tbdep(dno) on delete cascade
  • –now delete record from table
  • delete from tbdep where dno=10
  • –See Table Records
  • select *From tbemp
  • select *From tbdep
  • –Set Null Method
  • alter table tbemp drop constraint fk_dno
  • ————
  • alter table tbemp add constraint fk_dno foreign key(edno) references tbdep(Dno) on delete set null
  • ———
  • delete from tbdep where dno=20
  • ———
  • select *from tbemp
  • –Set Default Method
  • alter table tbemp drop constraint fk_dno
  • alter table tbemp add constraint fk_dno foreign key(edno) references tbdep(Dno) on delete set default
  • exec sp_helpconstraint tbemp
  • delete from tbdep where dno=30
  • select *from tbemp
  • select *from tbdep
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s