Reason why data is not validated enable constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reason why data is not validated enable constraint

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2010-03-08 : 02:14:15

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.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-03-11 : 00:47:18

Use WITH CHECK option when you are enabling the constraint.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources