Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.
You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.
|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.
|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...

> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL
|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.
|||Thank you for your response. I have not tried this way of loading the data. I
will definately try this out.
"Gert-Jan Strik" wrote:

> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
>
|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:

> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> enable
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...

> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enab
le
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Thank you for your response. I have not tried this way of loading the data.
I
will definately try this out.
"Gert-Jan Strik" wrote:

> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
>|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:

> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
>
> enable
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Thank you for your response. I have not tried this way of loading the data. I
will definately try this out.
"Gert-Jan Strik" wrote:
> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
> >
> > Isn't there a way, other then Enterprise Manager, to disable and / or enable
> > constraints, in particular primary and foreign keys? I am migrating data
> > daily from one system to SQL and to disable and enable manually is
> > inconvenient and combersome. I looked through BOL and cannot find a direct
> > answer on how to create a process to automatically disable and / or enable
> > constraints. Thanks.
>|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:
> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> > Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> > constraints, in particular primary and foreign keys?
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints and query plans

Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny
It depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny
|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||Actually, YES:
http://www.microsoft.com/technet/abo...ps_122104.mspx
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>
|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.
|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/abo...ps_122104.mspx
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>

Constraints and query plans

Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
DannyIt depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Actually, YES:
http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_122104.mspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_122104.mspx
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
>> Does having enforced foreign key constraints between tables help query
>> performance?
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>> Does having enforced foreign key constraints between tables help query
>> performance? In our reporting database we have many views with multiple
>> joins so that report writing is easier. But with each additional join
>> the
>> optimizer generally scans or seeks the index on all joined tables whether
>> or not the query requests columns from the table. Currently there are no
>> foreign key constraints, by adding and enforcing them would the queries
>> produce better plans?
>> Thanks,
>> Danny
>

Constraints and query plans

Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
DannyIt depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Actually, YES:
http://www.microsoft.com/technet/ab...04.mspx

Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/ab...04.ms
px
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>

Constraints and Foreign Keys....

Hi,
I am currently in the design phase. I have a question about the possibility
of creating a certain type of constraint. So far, I don't think its
possible.
For the sake of the example say I have two entities, Trees and Cars. I keep
track of entities in another entity called Entity Types. This would contain
car and tree as a record. Then I have another list of entities called
characteristics. Certain charateristics will only apply to the car, others
to the tree and some to both. Example characteristics would be model,
manufacturer, species, and name.
So I created an Entity Assignable Charateristics. This will hold which
charateristics can be applied to which entity. Some of those will be
required, others won't. I could add an attribute "Required" to keep track o
f
this.
Charateristics for instances cars or trees will be stored in either Entity
Charateristics or in Car Charateristics and Tree Charateristics.
Here's my question. If in Entity Assignable Charateristics there are
indicated certain required charateristics for trees, is there a way I can
enforce that in Entity Charateristics or Tree Charateristics.
Thanks in advance.
Pete>> I have two entities, Trees and Cars. I keep track of entities in another
entity called Entity Types. <<
This is an OO approach to the world and NOT an RDBMS. Separate types
of entieis have their own tables; there is no such monster as a
"magical, vague, general thingie" set. To be is to be something in
particular, not everythign in general.|||Start by googling for "EAV design flaws".
Inability to impose appropriate integrity constraints is the primary
drawback of approaches similar to the one you propose.
Anith|||Hi,
Thanks for the response. I just want to make sure I understand you
correctly. You think that I should have Trees and Cars. Then you think I
should have Tree Assignable Charaterisics and Car Assignable Charateristics.
These hold all the possible charateristics for a tree or car respectively.
This makes sense.
Now if I create a Tree Charateristics entity, which holds all the
charateristics for each instance of a tree, how can I make sure that the
charateristics that are placed into this entity come from the Tree Assignabl
e
Charateristics subset or Charateristics?
Additionally, how would required charateristics play into this?
Thanks again.
Pete
"--CELKO--" wrote:

> This is an OO approach to the world and NOT an RDBMS. Separate types
> of entieis have their own tables; there is no such monster as a
> "magical, vague, general thingie" set. To be is to be something in
> particular, not everythign in general.
>|||Hi,
Thanks for the comment. The reason I have the entities I do is so people
can easily create a new tree or car or charateristic. However, one person
may wish to make the charaterisic model for a car required, another may not.
Since you seem to think I have a flaw in my design, could you suggest anothe
r
way I might accomplish what it is I am trying to do? Thanks again.
Peter
"Anith Sen" wrote:

> Start by googling for "EAV design flaws".
> Inability to impose appropriate integrity constraints is the primary
> drawback of approaches similar to the one you propose.
> --
> Anith
>
>|||>> The reason I have the entities I do is so people can easily create a new
Without a comprehensive knowledge of your business model, others cannot
possibly determine the characteristics of an entity of your enterprise.
Rather than thinking about trees, cars and characteristics, think of trees
and cars as a set of entities having their own attributes. In loose terms,
each form an entity type & therefore belong to separate tables.
Think of each table as a set of propositions, or statement of facts.
In a nutshell, with a relational database, rows represent facts about real
world entities, column values represent their properties and relational
operations answer the questions about them.
Anith|||Thanks for the comments. Let me start by saying I agree with your statement
.
However, it is a business requirement that a customer can create custom
attributes. That is in essence what I need to figure out.
I certainly do not want to give the user the ability to add a column to a
table, so it seems as though I need to use this EAV design. I am troubled b
y
it since I seem to be losing the ability to put certain restraints on the
data. Do you know of another way to handle custom attributes?
Thanks again.
"Anith Sen" wrote:

> Without a comprehensive knowledge of your business model, others cannot
> possibly determine the characteristics of an entity of your enterprise.
> Rather than thinking about trees, cars and characteristics, think of trees
> and cars as a set of entities having their own attributes. In loose terms,
> each form an entity type & therefore belong to separate tables.
>
> Think of each table as a set of propositions, or statement of facts.
> In a nutshell, with a relational database, rows represent facts about real
> world entities, column values represent their properties and relational
> operations answer the questions about them.
> --
> Anith
>
>|||Pete,
On what basis does a customer create those attributes? How does one make
sure the attributes are appropriate to the entity that is being represented
in the table? How does one constrain the values to be drawn from a domain
specific to that attribute?
It sure sounds like a serious misunderstanding of business requirements.
There is no such thing as a custom attribute. Either an entity has an
attribute or it does not. It is up to the database designer to determine all
the applicable attributes of relevance pertaining to an entity in the
conceptual model, before starting logical design. In general, when one
chooses to ignore this exercise, often he is forced to compensate by forcing
the user to share the burden of creating tables, columns, constraints etc.
Generally, the database design flows from conceptual -> logical -> physical
levels.
-- Understand the business model thoroughly so that you will know the
entities, relationships among them and the attributes involved.
-- From this business model ( also known as conceptual schema ) applying
relational principles, map them relational tables. Use proven design
guidelines like normalization, predicate uniqueness principle etc. to
eliminate data redundancy. A good relational design allows the data to be
represented without application or task bias.
-- Based on the capabilities of the SQL DBMS, tune up the physical model so
the data can be efficiently manipulated and retrieved.
Many a time, designers introduce complexity at the logical level in an
attempt to compensate for an incomplete business model. Similarly complexity
is often introduced at the physical level when trying to compensate for a
mediocre logical model as well.
Anith|||Yes, it is the same. Initially, they called it New Design Priciple and later
renamed to the Principle of Orthogonal design.
Anith|||Thanks for the response. These are all questions I am grappling with. The
example that I started with illustrates this point well. I need to create
new trees, new cars and new charateristics that could be applied to either.
We are an ISV so customers are supposed to be able to create their own
characteristics (i.e. attributes). If you know of another way to handle thi
s
(without changing the table structure), please let me know. Any thoughts
would be great.
Thanks again.
"Anith Sen" wrote:

> Pete,
>
> On what basis does a customer create those attributes? How does one make
> sure the attributes are appropriate to the entity that is being represente
d
> in the table? How does one constrain the values to be drawn from a domain
> specific to that attribute?
> It sure sounds like a serious misunderstanding of business requirements.
>
> There is no such thing as a custom attribute. Either an entity has an
> attribute or it does not. It is up to the database designer to determine a
ll
> the applicable attributes of relevance pertaining to an entity in the
> conceptual model, before starting logical design. In general, when one
> chooses to ignore this exercise, often he is forced to compensate by forci
ng
> the user to share the burden of creating tables, columns, constraints etc.
> Generally, the database design flows from conceptual -> logical -> physica
l
> levels.
> -- Understand the business model thoroughly so that you will know the
> entities, relationships among them and the attributes involved.
> -- From this business model ( also known as conceptual schema ) applying
> relational principles, map them relational tables. Use proven design
> guidelines like normalization, predicate uniqueness principle etc. to
> eliminate data redundancy. A good relational design allows the data to be
> represented without application or task bias.
> -- Based on the capabilities of the SQL DBMS, tune up the physical model s
o
> the data can be efficiently manipulated and retrieved.
> Many a time, designers introduce complexity at the logical level in an
> attempt to compensate for an incomplete business model. Similarly complexi
ty
> is often introduced at the physical level when trying to compensate for a
> mediocre logical model as well.
> --
> Anith
>
>

Constraints

Can any one tell me regarding the foreign key constraints like I wanna
findout which columns doesn't contain the foreign key constraint so that I
can create it...
Thanks in advance.
Hi Joh,
Why do you want create Foreign key constrain for all the columns in a table
, Probably i understood your question wrongly.
What you could do is all the constrains specificatins can be view in the
below information schema view. Have a look into it and analyze ur
requirement.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Can any one tell me regarding the foreign key constraints like I wanna
> findout which columns doesn't contain the foreign key constraint so that I
> can create it...
>
> Thanks in advance.
>
|||No you took my question wrongly... I want to make sure Primary Key &
Foreign Key constraints are there in my database... nothing would be missed
for instance...
Emp Dept
EmpID DeptID
DeptID DeptName
EmpName
According to the above situation I have to make sure in the emp table there
should be a foreign key constraint on DeptID column.. Is there any query
through which I can make sure about it... hope now you understand my
question.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi Joh,
> Why do you want create Foreign key constrain for all the columns in a
table[vbcol=seagreen]
> , Probably i understood your question wrongly.
> What you could do is all the constrains specificatins can be view in the
> below information schema view. Have a look into it and analyze ur
> requirement.
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
I
>
|||Hi,
I dont have a query for this with me. I personally recommend you to use MS
Visio / Erwin to do the logical / physical data model with all relationship.
But for validating you could use INFORMATION_SCHEMA.TABLE_CONSTRAINTS system
view.
Thanks
Hari
SQL Server MVP
can you please work with the system view
INFORMATION_SCHEMA.TABLE_CONSTRAINTS? This will help
"Joh" <joh@.mailcity.com> wrote in message
news:OAQTHygYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> No you took my question wrongly... I want to make sure Primary Key &
> Foreign Key constraints are there in my database... nothing would be
> missed
> for instance...
> Emp Dept
> EmpID DeptID
> DeptID DeptName
> EmpName
> According to the above situation I have to make sure in the emp table
> there
> should be a foreign key constraint on DeptID column.. Is there any query
> through which I can make sure about it... hope now you understand my
> question.
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> table
> I
>

constraint view

Hi,

I want to know which table my foreign is reference to

for instance I have this statement

ALTER TABLE BANK ADD CONSTRAINT BANKING_R_154 FOREIGN KEY (
COM_CODE ,
ACCOUNT_CODE ) references BANK_GLT

I want execute ine select * from information_schema.I_don't_know

to get the table BANK_GLT from constraint BANKING_R_154

How can I do this?

cheers,

Alessandro

Select * from INFORMATION_SCHEMA.Referential_Constraints RC

INNER JOIN INFORMATION_SCHEMA.Constraint_Column_usage CCU

ON RC.Constraint_Catalog = CCU.Constraint_Catalog AND

RC.Constraint_Schema = CCU.Constraint_Schema AND

RC.Constraint_Name = CCU.Constraint_Name

Where Table_Name = 'SomeTable' --for a table, use Constraint_Name for querying on Constraint Names
--

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks,

but, I would like to know which table my tables is reference to and which columns as well

cheers,

Constraint using SQL query?

I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
....random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
Amit
Amit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Constraint using SQL query?

I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
.);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Constraint using SQL query?

I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/