June 6, 2008 at 8:32 am
I have a bunch of non-trusted constraints in my database:
USE dbName:
SELECT object_name(parent_obj), name
FROM SYSOBJECTS WHERE OBJECTPROPERTY (ID, 'CNSTISNOTTRUSTED') = 1
I attempted to make them trusted like this:
ALTER TABLE tblName WITH CHECK CHECK CONSTRAINT constraintName;
Yet, even after running the above on all of them, the constraints remain non-trusted!
Why is this happening?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 6, 2008 at 8:43 am
Have you tried dropping and re-creating them? (With Check, of course.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 6, 2008 at 8:48 am
GSquared (6/6/2008)
Have you tried dropping and re-creating them? (With Check, of course.)
Good idea! It's a 3rd party db so I will restore it on my local server and test out dropping and recreating the constraints there.
Thanks for the suggestion!
(Still puzzled why the "normal" way did not work...)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 6, 2008 at 9:47 am
ok, this does not make any sense to me... :unsure:
I drop the constraint:
IF EXISTS (SELECT * FROM dbo.sysforeignkeys WHERE constid = OBJECT_ID(N'[dbo].[R_100]')
AND fkeyid = OBJECT_ID(N'[dbo].[tblName]'))
ALTER TABLE [dbo].[tblName] DROP CONSTRAINT [R_100]
I add the constraint with CHECK:
ALTER TABLE [dbo].[tblName] WITH CHECK
ADD
CONSTRAINT [R_100] FOREIGN KEY
(
[tID]
) REFERENCES [dbo].[tbl2] (
[tID]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
GO
I ensure it is made trusted:
ALTER TABLE [dbo].[tblName] WITH CHECK CHECK CONSTRAINT [R_100];
I run the following and I still see constraint listed!! :crazy:
USE dbName;
SELECT object_name(parent_obj), name
FROM SYSOBJECTS WHERE OBJECTPROPERTY (ID, 'CNSTISNOTTRUSTED') = 1;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 6, 2008 at 11:56 am
What does it give you if you run this query:
SELECT OBJECT_NAME(parent_object_id) AS table_name, name
FROM sys.check_constraints
WHERE is_not_trusted = 1
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 6, 2008 at 12:00 pm
GSquared (6/6/2008)
What does it give you if you run this query:
SELECT OBJECT_NAME(parent_object_id) AS table_name, name
FROM sys.check_constraints
WHERE is_not_trusted = 1
You caught me, I'm on 2000... :blush:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 6, 2008 at 12:13 pm
Can you check if the constraints are DISABLED ?
OBJECTPROPERTY ( id , 'CnstIsDisabled')
* Noel
June 6, 2008 at 12:23 pm
noeld (6/6/2008)
Can you check if the constraints are DISABLED ?OBJECTPROPERTY ( id , 'CnstIsDisabled')
This returns no rows, ie. all constraints are enabled.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 6, 2008 at 12:41 pm
Ahhhh....
You have set NOT FOR REPLICATION. With that setting ON SQL Server considers the constraint as UNTRUSTED.
* Noel
June 6, 2008 at 1:12 pm
noeld (6/6/2008)
Ahhhh....You have set NOT FOR REPLICATION. With that setting ON SQL Server considers the constraint as UNTRUSTED.
Thank you! But that is bad isn't it?
This is a 3rd party db, and I probably should not attempt to change this...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 6, 2008 at 2:01 pm
Well if you are a subscriber database then you don't have a choice.
But if you are not replicating or you are a publisher you can get rid of it 😉
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply