November 23, 2009 at 7:57 am
I need to disable and enable constraint to update a primary key. I was successfully able to disable the constraint and update the primary key and foreign keys related to it. And I enabled it.
I think that sql server does not do the constraint violation from enable syntax.
for example, alter table table_name check constraint.
I am wondering if there is a way to do the constraint violation from the sql. It's because I am executing it from java jdbc.
I read that there is a way to do it from DBCC command. But I need to take care of it with sql.
Please help if there is a way.
Thanks.
November 23, 2009 at 8:18 am
You can enable the check constraint with the check option. Check the script bellow that shows it:
use tempdb
go
--creating the table with check constraint
create table demo (i int constraint CK_Demo_i_le10 check (i<=10))
go
--check that the constraint can be trusted
select objectproperty(object_id('CK_Demo_i_le10'),'CnstIsNotTrusted')
go
--alter table and disable the trigger
alter table demo nocheck constraint CK_Demo_i_le10
go
--alter table and enable the trigger
alter table demo check constraint CK_Demo_i_le10
go
--The constraint is still not trusted
select objectproperty(object_id('CK_Demo_i_le10'),'CnstIsNotTrusted')
go
--alter table and enable the trigger with the check option
--(notice the adition of the with check in the statement)
alter table demo with check check constraint CK_Demo_i_le10
--now the constraint is trusted trusted
select objectproperty(object_id('CK_Demo_i_le10'),'CnstIsNotTrusted')
go
--cleanup
drop table demo
go
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 23, 2009 at 1:01 pm
Thanks..
But I have multiple schemas. So I tried like this.
select objectproperty(object_id('RSC'),'schemaName.tableName');
Is this correct? And it seems like that it is returning something. what is it returning?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply