Constraint Enabling

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  March 8, 2010 at 12:16 am

  #74596

  Hi,

  Want to know the reason behind why the sql server is not validating the existing data once i enable a constraint ??

  drop table dept

  drop table emp

  create table dept

  (deptno int not null primary key )

  insert into dept

  select 10

  union all

  select 20

  union all

  select 30

  create table emp

  (empno int not null primary key,

  deptno int

  )

  alter table emp

  add constraint fk_emp_deptno

  foreign key (deptno) references dept(deptno)

  insert into emp

  select 1,10

  --disable the constraint

  alter table emp nocheck constraint fk_emp_deptno;

  -- INSERT wrong DEPTNO in EMP table

  insert into emp

  select 2,60

  alter table emp check constraint fk_emp_deptno;

  Here when we ENABLE the constraint, itshould validate the existing data but why it is not done?

  Thanks in Advance.

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  March 10, 2010 at 4:01 am

  #1131248

  Answer to myself... 🙂

  --Solution

  alter table emp WITH CHECK CHECK constraint fk_emp_deptno;