List all the relations without constraints

On Tue, 3 Nov 2009 04:13:14 -0800 (PST), Christo Labuschagne wrote:

>Good day.
>
>I'm working on a db that was created a few years ago (long before my
>time), and we found out (by accident) that there was a relationship
>that did not check for the constraints. Some of the parents were
>deleted causing orphan records. Is there a built in sp that can check
>for relations that do not have constrains? I did find a few built in
>views that shows data about relations, but I could not see any field
>that indicated the check status.
>
>Any help would be appreciated.
>
>Kind regards
>Christo

Hi Christo,

I think there's a communication mismatch here. Most people working with
SQL Server use the term "relation" for a foreign key relationship, that
(as the name implies) is enforced using a FOREIGH KEY constraint. But I
get the feeling you don't.

So what relations are you talking about? Where are they documented? Does
SQL Server even know about them, and if so - how?

If the relations are only documented in some external tool, then we
can't help you. You'll have to dig in the documentation of that tool to
find if it supports some way to verify an implemented database against
the logical model and propose corrections.

If the relationships are known in SQL Server, then there might be some
way to help you - as soon as I understand HOW and WHERE they are
documented.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: List all the relations without constraints 

Hi all

From the top:
Uri:
LOL on your explanation. Yes, in a nutshell, that is it. You can
have a relationship between two tables without the constraint being
enabled. In this case, you will always have the "line" and key drawn
between the two tables in GUI table designers, but you can still
delete the parent record while a child is still linked.

Russell and Hugo:
I use Microsoft SQL server Management Studio. If you right click
on the table and select design, you will be able to click on an icon
in the top left (on my comp, might be different on yours) with a
picture of 3 tables and 2 connections between them (tool tip is
relationships). This will bring up a dialog with all the
relationships on that table. If you select one it will show the
settings on the right. There is a property "Enforce Foreign Key
Constraint". If you select no, you get my problem.

The table create script then include this:

ALTER TABLE [dbo].[ChildTable] WITH NOCHECK ADD CONSTRAINT
[FK_ChildTable_ParentTable] FOREIGN KEY([FKParentTable])
REFERENCES [dbo].[ParentTable] ([IDParentTable])
GO

ALTER TABLE [dbo].[ChildTable] NOCHECK CONSTRAINT
[FK_ChildTable_ParentTable]
GO


instead of


ALTER TABLE [dbo].[ChildTable] WITH NOCHECK ADD CONSTRAINT
[FK_ChildTable_ParentTable] FOREIGN KEY([FKParentTable])
REFERENCES [dbo].[ParentTable] ([IDParentTable])
GO

ALTER TABLE [dbo].[ChildTable] CHECK CONSTRAINT
[FK_InspectionResult_ParentTable]
GO

The difference is in the second command with the CHECK / NONCHECK. My
question is: Is there a way to go through all the tables and make
sure the person did not take off any of the other constraint way back
when?

Kind regards
Christo

On Nov 4, 12:56 am, Hugo Kornelis
wrote:
> On Tue, 3 Nov 2009 04:13:14 -0800 (PST), Christo Labuschagne wrote:
> >Good day.
>
> >I'm working on a db that was created a few years ago (long before my
> >time), and we found out (by accident) that there was a relationship
> >that did not check for the constraints.  Some of the parents were
> >deleted causing orphan records.  Is there a built in sp that can check
> >for relations that do not have constrains?  I did find a few built in
> >views that shows data about relations, but I could not see any field
> >that indicated the check status.
>
> >Any help would be appreciated.
>
> >Kind regards
> >Christo
>
> Hi Christo,
>
> I think there's a communication mismatch here. Most people working with
> SQL Server use the term "relation" for a foreign key relationship, that
> (as the name implies) is enforced using a FOREIGH KEY constraint. But I
> get the feeling you don't.
>
> So what relations are you talking about? Where are they documented? Does
> SQL Server even know about them, and if so - how?
>
> If the relations are only documented in some external tool, then we
> can't help you. You'll have to dig in the documentation of that tool to
> find if it supports some way to verify an implemented database against
> the logical model and propose corrections.
>
> If the relationships are known in SQL Server, then there might be some
> way to help you - as soon as I understand HOW and WHERE they are
> documented.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: List all the relations without constraints 

On Wed, 4 Nov 2009 06:30:57 -0800 (PST), Christo Labuschagne wrote:

>Hi all
(snip)

Hi Christo,

Thanks for the excellent explanation. What you describe is actually not
a relation without a constraint, but a constraint that has been
disabled. That is, it's definition is still there, but SQL Server has
been told not to enforce it.

You can easily find which other constraints are disabled by running this
query:

SELECT OBJECT_NAME(parent_object_id) AS Table_name,
[name] AS Constraint_name
FROM sys.foreign_keys
WHERE is_disabled = 1;

You might also be interested in the foreign key constraints with
is_disabled equal to 0 but is_not_trusted equal to 1. This indicates
that the constraint is enabled now, but has at one point been disabled
and the existing population has not been checked when the constrained
was re-enabled.

Note also the syntax for re-enabling these constraints:

ALTER TABLE xxx CHECK CONSTRAINT yyy;

only re-enables but does not check existing population. To make sure all
rows in the database adhere to the constraint, use

ALTER TABLE xxx WITH CHECK CHECK CONSTRAINT yyy;

And indeed, there are two occurences of the word CHECK there!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: List all the relations without constraints 

On Nov 5, 12:02 am, Hugo Kornelis
wrote:
> On Wed, 4 Nov 2009 06:30:57 -0800 (PST), Christo Labuschagne wrote:
> >Hi all
>
> (snip)
>
> Hi Christo,
>
> Thanks for the excellent explanation. What you describe is actually not
> a relation without a constraint, but a constraint that has been
> disabled. That is, it's definition is still there, but SQL Server has
> been told not to enforce it.
>
> You can easily find which other constraints are disabled by running this
> query:
>
> SELECT OBJECT_NAME(parent_object_id) AS Table_name,
>       [name] AS Constraint_name
> FROM   sys.foreign_keys
> WHERE  is_disabled = 1;
>
> You might also be interested in the foreign key constraints with
> is_disabled equal to 0 but is_not_trusted equal to 1. This indicates
> that the constraint is enabled now, but has at one point been disabled
> and the existing population has not been checked when the constrained
> was re-enabled.
>
Hugo, you are a legend!

Sorry for phrasing the question wrong. Whoops!

Luckily only the one relationship had the constraint DISABLED ,
but there are 105 relationships that are not trusted, so I'm going to
try and change that now.

Thanx again for your help and God bless!

Regards
Christo


> Note also the syntax for re-enabling these constraints:
>
> ALTER TABLE xxx CHECK CONSTRAINT yyy;
>
> only re-enables but does not check existing population. To make sure all
> rows in the database adhere to the constraint, use
>
> ALTER TABLE xxx WITH CHECK CHECK CONSTRAINT yyy;
>
> And indeed, there are two occurences of the word CHECK there!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: List all the relations without constraints 

Christo

SELECT 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+ ' WITH CHECK CHECK
CONSTRAINT '+ name

FROM sys.check_constraints

WHERE is_not_trusted = 1

UNION ALL

SELECT 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+ ' WITH CHECK CHECK
CONSTRAINT '+ name

FROM sys.foreign_keys

WHERE is_not_trusted = 1





"Christo Labuschagne" wrote in message

On Nov 5, 12:02 am, Hugo Kornelis
wrote:
> On Wed, 4 Nov 2009 06:30:57 -0800 (PST), Christo Labuschagne wrote:
> >Hi all
>
> (snip)
>
> Hi Christo,
>
> Thanks for the excellent explanation. What you describe is actually not
> a relation without a constraint, but a constraint that has been
> disabled. That is, it's definition is still there, but SQL Server has
> been told not to enforce it.
>
> You can easily find which other constraints are disabled by running this
> query:
>
> SELECT OBJECT_NAME(parent_object_id) AS Table_name,
> [name] AS Constraint_name
> FROM sys.foreign_keys
> WHERE is_disabled = 1;
>
> You might also be interested in the foreign key constraints with
> is_disabled equal to 0 but is_not_trusted equal to 1. This indicates
> that the constraint is enabled now, but has at one point been disabled
> and the existing population has not been checked when the constrained
> was re-enabled.
>
Hugo, you are a legend!

Sorry for phrasing the question wrong. Whoops!

Luckily only the one relationship had the constraint DISABLED ,
but there are 105 relationships that are not trusted, so I'm going to
try and change that now.

Thanx again for your help and God bless!

Regards
Christo


> Note also the syntax for re-enabling these constraints:
>
> ALTER TABLE xxx CHECK CONSTRAINT yyy;
>
> only re-enables but does not check existing population. To make sure all
> rows in the database adhere to the constraint, use
>
> ALTER TABLE xxx WITH CHECK CHECK CONSTRAINT yyy;
>
> And indeed, there are two occurences of the word CHECK there!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: List all the relations without constraints 

Hehe, it really does seem like I'm a newb!

I did it like this:

declare C cursor for
SELECT OBJECT_NAME(parent_object_id) AS Table_name, name AS
Constraint_name
FROM sys.foreign_keys
WHERE (is_disabled = 1) OR (is_not_trusted = 1)

DECLARE @Tablename varchar(100), @Constraint_name varchar(100), @SQL
varchar(max)

open C
fetch next from C into @Tablename, @Constraint_name

while @@fetch_status = 0
begin
set @SQL = 'ALTER TABLE ' + @Tablename + ' WITH CHECK CHECK
CONSTRAINT ' + @Constraint_name
print @SQL
exec (@SQL)
fetch next from C into @Tablename, @Constraint_name
end

close C
deallocate c

But yours seem a lot better :-$. O well, at least my boss is happy!

Thanx for the hint! An so we learn!

Blessings!
Christo


On Nov 5, 12:06 pm, "Uri Dimant" wrote:
> Christo
>
> SELECT 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+ ' WITH CHECK CHECK
> CONSTRAINT '+ name
>
> FROM sys.check_constraints
>
> WHERE is_not_trusted = 1
>
> UNION ALL
>
> SELECT 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+ ' WITH CHECK CHECK
> CONSTRAINT '+ name
>
> FROM sys.foreign_keys
>
> WHERE is_not_trusted = 1
>
> "Christo Labuschagne" wrote in message
>
>
> On Nov 5, 12:02 am, Hugo Kornelis
>
> wrote:
> > On Wed, 4 Nov 2009 06:30:57 -0800 (PST), Christo Labuschagne wrote:
> > >Hi all
>
> > (snip)
>
> > Hi Christo,
>
> > Thanks for the excellent explanation. What you describe is actually not
> > a relation without a constraint, but a constraint that has been
> > disabled. That is, it's definition is still there, but SQL Server has
> > been told not to enforce it.
>
> > You can easily find which other constraints are disabled by running this
> > query:
>
> > SELECT OBJECT_NAME(parent_object_id) AS Table_name,
> > [name] AS Constraint_name
> > FROM sys.foreign_keys
> > WHERE is_disabled = 1;
>
> > You might also be interested in the foreign key constraints with
> > is_disabled equal to 0 but is_not_trusted equal to 1. This indicates
> > that the constraint is enabled now, but has at one point been disabled
> > and the existing population has not been checked when the constrained
> > was re-enabled.
>
> Hugo, you are a legend!
>
> Sorry for phrasing the question wrong.  Whoops!
>
> Luckily only the one relationship had the constraint DISABLED ,
> but there are 105 relationships that are not trusted, so I'm going to
> try and change that now.
>
> Thanx again for your help and God bless!
>
> Regards
> Christo
>
> > Note also the syntax for re-enabling these constraints:
>
> > ALTER TABLE xxx CHECK CONSTRAINT yyy;
>
> > only re-enables but does not check existing population. To make sure all
> > rows in the database adhere to the constraint, use
>
> > ALTER TABLE xxx WITH CHECK CHECK CONSTRAINT yyy;
>
> > And indeed, there are two occurences of the word CHECK there!
>
> > --
> > Hugo Kornelis, SQL Server MVP
> > My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: List all the relations without constraints 

On Thu, 5 Nov 2009 01:13:36 -0800 (PST), Christo Labuschagne wrote:

>Hugo, you are a legend!

Wow, that's the first time anyone said this to me. Are legends not
supposed to be dead? (Oops)

>Sorry for phrasing the question wrong. Whoops!

You didn;t phrase it wrong, you had to learn the terminology. No
problem, that's what we're here for!

>Luckily only the one relationship had the constraint DISABLED ,
>but there are 105 relationships that are not trusted, so I'm going to
>try and change that now.
>
>Thanx again for your help and God bless!

You're welcome - and I hope you don't find too many orphaned rows when
checking those 105 relationships.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: List all the relations without constraints 

On Thu, 5 Nov 2009 07:16:17 -0800 (PST), Christo Labuschagne wrote:

>Hehe, it really does seem like I'm a newb!
>
>I did it like this:

Which is a perfectly valid method as well. Except that Uri threw in a
test for non trusted CHECK constraints as well. (A very sensible thing
to do, BTW).

Cursors in production code can often (not always!) hurt performance, but
for one-shot maintenance tasks suchh as this one, they can be a great
tool!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: List all the relations without constraints