If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
ALL). Is there any command to verify that the constraints are disabled or
don't exist.Check out sp_helpconstraint in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SQL_Learner" <SQL_Learner@.discussions.microsoft.com> wrote in message
news:E615AA31-2B87-4B85-BC6B-6CC01BF0D8C8@.microsoft.com...
If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
ALL). Is there any command to verify that the constraints are disabled or
don't exist.|||Thanks Tom, this command is what I am looking for.
In SQL server, without checking if we enable all constraints (irrespective
of they are enabled or disabled at that point) are the any issues? What are
your thoughts on this.
Thanks in Advance
"Tom Moreau" wrote:
> Check out sp_helpconstraint in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "SQL_Learner" <SQL_Learner@.discussions.microsoft.com> wrote in message
> news:E615AA31-2B87-4B85-BC6B-6CC01BF0D8C8@.microsoft.com...
> If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
> ALL). Is there any command to verify that the constraints are disabled or
> don't exist.
>|||That depends. If you attempt to enable the constraints WITH CHECK, and
there are existing violations of those constraints, then re-enabling will
fail. However, if you re-enable WITH NOCHECK, then it will succeed.
That said, if you have a partitioned view, then you'd want to use WITH
CHECK, so as to take advantage of the performance benefits that having such
constraints will give you.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SQL_Learner" <SQLLearner@.discussions.microsoft.com> wrote in message
news:62ABA723-4797-4C44-AE67-D0D9F853F6D6@.microsoft.com...
Thanks Tom, this command is what I am looking for.
In SQL server, without checking if we enable all constraints (irrespective
of they are enabled or disabled at that point) are the any issues? What are
your thoughts on this.
Thanks in Advance
"Tom Moreau" wrote:
> Check out sp_helpconstraint in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "SQL_Learner" <SQL_Learner@.discussions.microsoft.com> wrote in message
> news:E615AA31-2B87-4B85-BC6B-6CC01BF0D8C8@.microsoft.com...
> If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
> ALL). Is there any command to verify that the constraints are disabled or
> don't exist.
>|||I am enabling constraint in the following way:
STMT1: ALTER TABLE Customers CHECK CONSTRAINT ALL
Disabling as:
STMT2: ALTER TABLE Customers NOCHECK CONSTRAINT ALL
In disabled or enabled state, if I am running STMT1 again and again in the
Query Analyser, I am not getting any error. Does that mean in disabled state
the SQL server will enable the constraints and in enabled state it will
ignore to reenable the constraints?
What is the performance issue in this case where I don't have a partitioned
view.
Thanks!|||Yes, it will ignore things if they're already enabled.
In some cases, you could get a performance hit if you're doing a query like:
select
*
from
MyTable m
where exists
(
select
*
from
OtherTable o
where
o.FK = m.PK
)
... and you've disabled the foreign key from OtherTable to MyTable or
re-enabled it with NOCHECK. The optimizer can take advantage of the fact
that it knows something about the data in OtherTable, due to the constraint.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SQL_Learner" <SQLLearner@.discussions.microsoft.com> wrote in message
news:C227BE86-B58A-4739-B435-F737427C900B@.microsoft.com...
I am enabling constraint in the following way:
STMT1: ALTER TABLE Customers CHECK CONSTRAINT ALL
Disabling as:
STMT2: ALTER TABLE Customers NOCHECK CONSTRAINT ALL
In disabled or enabled state, if I am running STMT1 again and again in the
Query Analyser, I am not getting any error. Does that mean in disabled state
the SQL server will enable the constraints and in enabled state it will
ignore to reenable the constraints?
What is the performance issue in this case where I don't have a partitioned
view.
Thanks!|||Thanks Tom for your post!
Showing posts with label constraints. Show all posts
Showing posts with label constraints. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
Check Constraints or Triggers
Hi, Im facing teh following situation:
This are just sample table names, but should do for discussing
purpouses.
Create table Invoice
(
InvoiceID Integer Not Null,
CustomerType Integer Not Null,
CustomerCode Integer Not Null,
Amount DECIMAL(10,2) Not Null,
.............
)
Create Table Type1Customer
(
CustomerCode Integer Not Null,
........................
)
Create Table Type2Customer
(
CustomerCode Integer Not Null,
........................
)
I need to add a way to restrict the CustomerType and CustomerCode,
in the Invoice table to the correct values.
This means that if customerType equals 1 the customerCode should be
checked against Type1Customer and if customerType equals 2 the
customerCode should be checked against Type2Customer.
I succesfully created a check constraint. That ensures that the valid
values exists when the rows in the Invoice table are inserted or
updated, but doesnt prevent from deleting records from tables
Type1Customer and Type2Customer that are referenced from the Invoice
table.
Are triggers the only way to go?
Thanks in advance
Sebastin streigerIn addition to Erland's suggestion,
I would recommend adding CustomerType to both Type1Customer and
Type2Customer, and adding CustomerType to their FK constraints|||(sebastian.streiger@.gmail.com) writes:
> This are just sample table names, but should do for discussing
> purpouses.
> Create table Invoice (
> InvoiceID Integer Not Null,
> CustomerType Integer Not Null,
> CustomerCode Integer Not Null,
> Amount DECIMAL(10,2) Not Null,
> ............. )
> Create Table Type1Customer (
> CustomerCode Integer Not Null,
> ....................... )
>
> Create Table Type2Customer (
> CustomerCode Integer Not Null,
> ....................... )
> I need to add a way to restrict the CustomerType and CustomerCode,
> in the Invoice table to the correct values.
> This means that if customerType equals 1 the customerCode should be
> checked against Type1Customer and if customerType equals 2 the
> customerCode should be checked against Type2Customer.
>...
> Are triggers the only way to go?
With that data model, yes. But is that really the right data model?
I would rather have a CustomerCode table which could look like this:
CREATE TABLE CustomerCode (
CustomerType integer NOT NULL,
CustomerCode integer NOT NULL,
CONSTRAINT pk_CustomerCode(CustomerType, CustomerCode))
Then Invoices could refer to this table, and so could the child
tables Type1Customer and Type2Customer.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and AK:
Thank you for answering.
I DO agree that the model is no the best one that we can have. But due
to organizational issues Im not in position to change the tables
structures by now. So, Im trying to add constraints to ensure the
data consistency.
Thanks for your valuable feedback
Check constraints on Tables within UDFs - cannot drop constraint l
This is probably obscure usage of the SQL Server feature-set, but any help
appreciated.
I attempted to include a CHECK constraint in the table-definition for the
RETURN table value of a UDF. Like this:
create function dbo.MyFunction ()
returns @.r table
( MyColumn int not null,
check (MyColumn in (1,2,3))
)
as
... ... ...
(Greatly simplified of course.)
I succeeded in having it create the constraint, as long as I (a) did not
name it, and (b) did it as a table constraint rather than inline with the
column definition. [These are also odd behaviors to me.]
However, when I later attempt to ALTER FUNCTION to apply a new version, I
get an error that it cannot alter the function because it is being reference
d
by another object, then gives the obviously system-generated name of the
CHECK constraint it created, apparently, under the hood.
It seems the only way to get rid of it now is to DROP the function (which I
do not like for other reasons, preferring "ALTER" until SQL Server gets an
Oracle-esque "create or replace" syntax going).
But outside of that, there seems to be no way to get rid of it. I can't
alter-function-drop-constraint, like one could with a table. And I can't jus
t
drop the constraint by itself.
Thoughts? Suggestions? Future feature request maybe?
It would be nice if table-valued functions were more closely aligned with
tables in functionality.
Eric M. Wilson
www.datazulu.comHi
Your finding seem to be correct! It does seem to be an obscure requirement
and I can not think of a reason why you would want to do this. The most
obvious way to get around it is to work with a table variable within the
function that has the constraint and remove it from the function.
If you have any requests for additional/changed functionality you can email
them too SQLWish@.microsoft.com
John
"Eric Wilson" wrote:
> This is probably obscure usage of the SQL Server feature-set, but any help
> appreciated.
> I attempted to include a CHECK constraint in the table-definition for the
> RETURN table value of a UDF. Like this:
> create function dbo.MyFunction ()
> returns @.r table
> ( MyColumn int not null,
> check (MyColumn in (1,2,3))
> )
> as
> ... ... ...
> (Greatly simplified of course.)
> I succeeded in having it create the constraint, as long as I (a) did not
> name it, and (b) did it as a table constraint rather than inline with the
> column definition. [These are also odd behaviors to me.]
> However, when I later attempt to ALTER FUNCTION to apply a new version, I
> get an error that it cannot alter the function because it is being referen
ced
> by another object, then gives the obviously system-generated name of the
> CHECK constraint it created, apparently, under the hood.
> It seems the only way to get rid of it now is to DROP the function (which
I
> do not like for other reasons, preferring "ALTER" until SQL Server gets an
> Oracle-esque "create or replace" syntax going).
> But outside of that, there seems to be no way to get rid of it. I can't
> alter-function-drop-constraint, like one could with a table. And I can't j
ust
> drop the constraint by itself.
> Thoughts? Suggestions? Future feature request maybe?
> It would be nice if table-valued functions were more closely aligned with
> tables in functionality.
> --
> Eric M. Wilson
> www.datazulu.com
appreciated.
I attempted to include a CHECK constraint in the table-definition for the
RETURN table value of a UDF. Like this:
create function dbo.MyFunction ()
returns @.r table
( MyColumn int not null,
check (MyColumn in (1,2,3))
)
as
... ... ...
(Greatly simplified of course.)
I succeeded in having it create the constraint, as long as I (a) did not
name it, and (b) did it as a table constraint rather than inline with the
column definition. [These are also odd behaviors to me.]
However, when I later attempt to ALTER FUNCTION to apply a new version, I
get an error that it cannot alter the function because it is being reference
d
by another object, then gives the obviously system-generated name of the
CHECK constraint it created, apparently, under the hood.
It seems the only way to get rid of it now is to DROP the function (which I
do not like for other reasons, preferring "ALTER" until SQL Server gets an
Oracle-esque "create or replace" syntax going).
But outside of that, there seems to be no way to get rid of it. I can't
alter-function-drop-constraint, like one could with a table. And I can't jus
t
drop the constraint by itself.
Thoughts? Suggestions? Future feature request maybe?
It would be nice if table-valued functions were more closely aligned with
tables in functionality.
Eric M. Wilson
www.datazulu.comHi
Your finding seem to be correct! It does seem to be an obscure requirement
and I can not think of a reason why you would want to do this. The most
obvious way to get around it is to work with a table variable within the
function that has the constraint and remove it from the function.
If you have any requests for additional/changed functionality you can email
them too SQLWish@.microsoft.com
John
"Eric Wilson" wrote:
> This is probably obscure usage of the SQL Server feature-set, but any help
> appreciated.
> I attempted to include a CHECK constraint in the table-definition for the
> RETURN table value of a UDF. Like this:
> create function dbo.MyFunction ()
> returns @.r table
> ( MyColumn int not null,
> check (MyColumn in (1,2,3))
> )
> as
> ... ... ...
> (Greatly simplified of course.)
> I succeeded in having it create the constraint, as long as I (a) did not
> name it, and (b) did it as a table constraint rather than inline with the
> column definition. [These are also odd behaviors to me.]
> However, when I later attempt to ALTER FUNCTION to apply a new version, I
> get an error that it cannot alter the function because it is being referen
ced
> by another object, then gives the obviously system-generated name of the
> CHECK constraint it created, apparently, under the hood.
> It seems the only way to get rid of it now is to DROP the function (which
I
> do not like for other reasons, preferring "ALTER" until SQL Server gets an
> Oracle-esque "create or replace" syntax going).
> But outside of that, there seems to be no way to get rid of it. I can't
> alter-function-drop-constraint, like one could with a table. And I can't j
ust
> drop the constraint by itself.
> Thoughts? Suggestions? Future feature request maybe?
> It would be nice if table-valued functions were more closely aligned with
> tables in functionality.
> --
> Eric M. Wilson
> www.datazulu.com
Check constraints on clustered columns
Hi guys 'n gals, I'm having an issue wrapping my head around a check constraint that I need to set on a table in my database.
Table: OnCall
Columns: OnCall_PKey (identity), Person_Key, StartDate, EndDate
When a new record is entered, I need a check constraint to make sure that the person entered does not already exist in the table with an overlapping time period:
If in the new record, the start date or the end date fall between the start date and the end date for an existing record having the person key in the new record then the record fails the check.
Example:
One existing data row from my table:
1496, 06/12/2007, 12/12/2007
I try to add:
1496, 09/12/2007, 15/12/2007
The record fails because the new date range overlaps the existing record in the table. No person can have overlapping time periods, however, a person can have multiple time slots in the table, it's just that none of the time slots may overlap.
Any pointers, will be gratefully received.What about defining a trigger on the table ?
Table: OnCall
Columns: OnCall_PKey (identity), Person_Key, StartDate, EndDate
When a new record is entered, I need a check constraint to make sure that the person entered does not already exist in the table with an overlapping time period:
If in the new record, the start date or the end date fall between the start date and the end date for an existing record having the person key in the new record then the record fails the check.
Example:
One existing data row from my table:
1496, 06/12/2007, 12/12/2007
I try to add:
1496, 09/12/2007, 15/12/2007
The record fails because the new date range overlaps the existing record in the table. No person can have overlapping time periods, however, a person can have multiple time slots in the table, it's just that none of the time slots may overlap.
Any pointers, will be gratefully received.What about defining a trigger on the table ?
Check constraints not being replicated properly
Hello, I'm at a loss with a tough problem I've yet to find a solution for:
SCENARIO:
I am replicating as a form of disaster-recovery. Our published application
uses local partitioned views, partitioned by month... so there is one
database per month. We have a date_created partitioning column in each
table which specifies that records should only go in that month. Example
for our Feb partition on the Processor_File table:
ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
[date_created]<'03/01/2008'))
When I add the tables as articles to the database's publication, I AM
setting the Copy Check Constraints = true option. I WANT this to occur,
because there's this crazy condition with partitioned views whereby the
check constraints can be enabled and yet somehow they are not "confident" in
the status of their checked data and therefore views that include the table
are considered non-updateable (which sucks -- can anybody explain THAT to me
either?!)
Regardless... I WANT the Check Constraints to be copied over AND I want them
enabled for replication. The problem, when the SNAPSHOT is generated to
create the database schema, it looks just like this:
IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
BEGIN
ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
[DF_Processor_File_date_created]
END
Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
want that clause to be set!!! That clause is _NOT_ set in the source
description of my published table. How can I make sure the snapshot does
not create the tables on the subscriber side with that option?
TIA for your support,
Eric Gavin
Alliance Payment Technologies
Do you want this constraint enforced on the subscriber when the DML is
occurring due to a replication process? If so you do not want that
constraint to be not for replication? It sounds like this is likely
something you don't want, but I don't think it is necessarily evil.
Please post your schema of the table with the constraints and I'll show you
how to create your publication.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
> Hello, I'm at a loss with a tough problem I've yet to find a solution for:
> SCENARIO:
> I am replicating as a form of disaster-recovery. Our published
> application uses local partitioned views, partitioned by month... so there
> is one database per month. We have a date_created partitioning column in
> each table which specifies that records should only go in that month.
> Example for our Feb partition on the Processor_File table:
> ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
> [date_created]<'03/01/2008'))
> When I add the tables as articles to the database's publication, I AM
> setting the Copy Check Constraints = true option. I WANT this to occur,
> because there's this crazy condition with partitioned views whereby the
> check constraints can be enabled and yet somehow they are not "confident"
> in the status of their checked data and therefore views that include the
> table are considered non-updateable (which sucks -- can anybody explain
> THAT to me either?!)
> Regardless... I WANT the Check Constraints to be copied over AND I want
> them enabled for replication. The problem, when the SNAPSHOT is generated
> to create the database schema, it looks just like this:
> IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
> BEGIN
> ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
> ([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
> ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
> [DF_Processor_File_date_created]
> END
> Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
> want that clause to be set!!! That clause is _NOT_ set in the source
> description of my published table. How can I make sure the snapshot does
> not create the tables on the subscriber side with that option?
> TIA for your support,
> Eric Gavin
> Alliance Payment Technologies
>
|||[Note to Microsoft folks -- I still need help please!]
Thanks for trying to help, but part of the frustration I have is that I seem
to WANT to be doing something that nobody else really wants to do. And so
I've scrolled through a million google posts trying to figure out how to do
what nobody else wants to do. I DO want to enforce the constraints at the
DML during replication because of the partitioned view requirements.
Not meaning to sound rude, but my original post has every answer to your
question already in it, so I'll just re-post it as a response:
[Repost]
> occurring due to a replication process?
> constraint to be not for replication?
[vbcol=seagreen]
Lastly, the schema of the constraint in the published table and the schema
that the snapshot generates has already been posted. May I ask why it would
be important to know the whole schema of the table?
Thanks, and I'm sorry if any offense was given. I have been at this for
quite some time now and am still frustrated because after reading the books
I appear to be doing everything right, but the snapshot just "refuses" to
create WITHOUT the "NOT FOR REPLICATION" option set during constraint
creation. I NEED that constraint to NOT be used when the snapshot as
applied at the subscriber.
Thanks,
Eric Gavin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23vC48aeWIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Do you want this constraint enforced on the subscriber when the DML is
> occurring due to a replication process? If so you do not want that
> constraint to be not for replication? It sounds like this is likely
> something you don't want, but I don't think it is necessarily evil.
> Please post your schema of the table with the constraints and I'll show
> you how to create your publication.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "AACH Developers" <AACHDevs@.community.nospam> wrote in message
> news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
>
|||Confusion posted by me on the last bit:
> Thanks, and I'm sorry if any offense was given. I have been at this for
> quite some time now and am still frustrated because after reading the
> books I appear to be doing everything right, but the snapshot just
> "refuses" to create WITHOUT the "NOT FOR REPLICATION" option set during
> constraint creation. I NEED that constraint to NOT be used when the
> snapshot as applied at the subscriber.
Please correct this to:
I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
is applied.
For further clarity, I must, want, desire and need the check constraints to
be checked during the application of DML because of replication.
Eric
|||UPDATE: Apparently the schema_option:
0x10000
Replicates CHECK constraints as NOT FOR REPLICATION so that the
constraints are not enforced during synchronization.
Exists, but BOL seems to leave out the small (yet necessary detail) that it
is simply un-settable from the GUI. So I think this problem is solved if
I'm willing to run sp_changearticles for all of my table articles with check
constraints (which I am.)
But if MS-folk read this and thinking I'm headed in the wrong direction, or
misguided in my belief that the UI doesn't allow for this setting to be
updated, I'd appreciate learning so. Thanks!!
Eric Gavin
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:e31zdvfWIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Confusion posted by me on the last bit:
>
> Please correct this to:
> I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
> is applied.
> For further clarity, I must, want, desire and need the check constraints
> to be checked during the application of DML because of replication.
> Eric
>
|||Hi Eric,
I understand that you would like to have the snapshot does not create the
tables on your subscriber with the NOT FOR REPLICATION clause.
If I have misunderstood, please let me know.
The option is a default option for SQL Server 2000/2005 replication when
there is any foreign key constraints or check constraints; however you can
programmatically use T-SQL statements to change the replication schema
options (schema_option) by yourself. Please refer to the following articles:
Frequently Asked Questions for Replication Administrators
-- How do I manage constraints on published tables?
http://msdn2.microsoft.com/en-us/library/ms151740.aspx
Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
http://msdn2.microsoft.com/en-us/library/ms152529.aspx
How to: Specify Schema Options (Replication Transact-SQL Programming)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_addarticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_changearticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms175980.aspx
For your other concern, "When I add the tables as articles to the
database's publication, I AM setting the Copy Check Constraints = true
option. I WANT this to occur, because there's this crazy condition with
partitioned views whereby the check constraints can be enabled and yet
somehow they are not "confident" in the status of their checked data and
therefore views that include the table are considered non-updateable (which
sucks -- can anybody explain THAT to me either?!)", I could not understand
it very clearly. To let me better understand your issue, could you please
answer me the following questions:
1. What it the type of your replication?
2. Did you mean that your views on your publisher became non-updatable
after you set the replication option "Copy Check Constraints = true" for
those table articles?
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
SCENARIO:
I am replicating as a form of disaster-recovery. Our published application
uses local partitioned views, partitioned by month... so there is one
database per month. We have a date_created partitioning column in each
table which specifies that records should only go in that month. Example
for our Feb partition on the Processor_File table:
ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
[date_created]<'03/01/2008'))
When I add the tables as articles to the database's publication, I AM
setting the Copy Check Constraints = true option. I WANT this to occur,
because there's this crazy condition with partitioned views whereby the
check constraints can be enabled and yet somehow they are not "confident" in
the status of their checked data and therefore views that include the table
are considered non-updateable (which sucks -- can anybody explain THAT to me
either?!)
Regardless... I WANT the Check Constraints to be copied over AND I want them
enabled for replication. The problem, when the SNAPSHOT is generated to
create the database schema, it looks just like this:
IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
BEGIN
ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
[DF_Processor_File_date_created]
END
Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
want that clause to be set!!! That clause is _NOT_ set in the source
description of my published table. How can I make sure the snapshot does
not create the tables on the subscriber side with that option?
TIA for your support,
Eric Gavin
Alliance Payment Technologies
Do you want this constraint enforced on the subscriber when the DML is
occurring due to a replication process? If so you do not want that
constraint to be not for replication? It sounds like this is likely
something you don't want, but I don't think it is necessarily evil.
Please post your schema of the table with the constraints and I'll show you
how to create your publication.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
> Hello, I'm at a loss with a tough problem I've yet to find a solution for:
> SCENARIO:
> I am replicating as a form of disaster-recovery. Our published
> application uses local partitioned views, partitioned by month... so there
> is one database per month. We have a date_created partitioning column in
> each table which specifies that records should only go in that month.
> Example for our Feb partition on the Processor_File table:
> ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
> [date_created]<'03/01/2008'))
> When I add the tables as articles to the database's publication, I AM
> setting the Copy Check Constraints = true option. I WANT this to occur,
> because there's this crazy condition with partitioned views whereby the
> check constraints can be enabled and yet somehow they are not "confident"
> in the status of their checked data and therefore views that include the
> table are considered non-updateable (which sucks -- can anybody explain
> THAT to me either?!)
> Regardless... I WANT the Check Constraints to be copied over AND I want
> them enabled for replication. The problem, when the SNAPSHOT is generated
> to create the database schema, it looks just like this:
> IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
> BEGIN
> ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
> ([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
> ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
> [DF_Processor_File_date_created]
> END
> Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
> want that clause to be set!!! That clause is _NOT_ set in the source
> description of my published table. How can I make sure the snapshot does
> not create the tables on the subscriber side with that option?
> TIA for your support,
> Eric Gavin
> Alliance Payment Technologies
>
|||[Note to Microsoft folks -- I still need help please!]
Thanks for trying to help, but part of the frustration I have is that I seem
to WANT to be doing something that nobody else really wants to do. And so
I've scrolled through a million google posts trying to figure out how to do
what nobody else wants to do. I DO want to enforce the constraints at the
DML during replication because of the partitioned view requirements.
Not meaning to sound rude, but my original post has every answer to your
question already in it, so I'll just re-post it as a response:
[Repost]
> occurring due to a replication process?
> constraint to be not for replication?
[vbcol=seagreen]
Lastly, the schema of the constraint in the published table and the schema
that the snapshot generates has already been posted. May I ask why it would
be important to know the whole schema of the table?
Thanks, and I'm sorry if any offense was given. I have been at this for
quite some time now and am still frustrated because after reading the books
I appear to be doing everything right, but the snapshot just "refuses" to
create WITHOUT the "NOT FOR REPLICATION" option set during constraint
creation. I NEED that constraint to NOT be used when the snapshot as
applied at the subscriber.
Thanks,
Eric Gavin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23vC48aeWIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Do you want this constraint enforced on the subscriber when the DML is
> occurring due to a replication process? If so you do not want that
> constraint to be not for replication? It sounds like this is likely
> something you don't want, but I don't think it is necessarily evil.
> Please post your schema of the table with the constraints and I'll show
> you how to create your publication.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "AACH Developers" <AACHDevs@.community.nospam> wrote in message
> news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
>
|||Confusion posted by me on the last bit:
> Thanks, and I'm sorry if any offense was given. I have been at this for
> quite some time now and am still frustrated because after reading the
> books I appear to be doing everything right, but the snapshot just
> "refuses" to create WITHOUT the "NOT FOR REPLICATION" option set during
> constraint creation. I NEED that constraint to NOT be used when the
> snapshot as applied at the subscriber.
Please correct this to:
I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
is applied.
For further clarity, I must, want, desire and need the check constraints to
be checked during the application of DML because of replication.
Eric
|||UPDATE: Apparently the schema_option:
0x10000
Replicates CHECK constraints as NOT FOR REPLICATION so that the
constraints are not enforced during synchronization.
Exists, but BOL seems to leave out the small (yet necessary detail) that it
is simply un-settable from the GUI. So I think this problem is solved if
I'm willing to run sp_changearticles for all of my table articles with check
constraints (which I am.)
But if MS-folk read this and thinking I'm headed in the wrong direction, or
misguided in my belief that the UI doesn't allow for this setting to be
updated, I'd appreciate learning so. Thanks!!
Eric Gavin
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:e31zdvfWIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Confusion posted by me on the last bit:
>
> Please correct this to:
> I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
> is applied.
> For further clarity, I must, want, desire and need the check constraints
> to be checked during the application of DML because of replication.
> Eric
>
|||Hi Eric,
I understand that you would like to have the snapshot does not create the
tables on your subscriber with the NOT FOR REPLICATION clause.
If I have misunderstood, please let me know.
The option is a default option for SQL Server 2000/2005 replication when
there is any foreign key constraints or check constraints; however you can
programmatically use T-SQL statements to change the replication schema
options (schema_option) by yourself. Please refer to the following articles:
Frequently Asked Questions for Replication Administrators
-- How do I manage constraints on published tables?
http://msdn2.microsoft.com/en-us/library/ms151740.aspx
Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
http://msdn2.microsoft.com/en-us/library/ms152529.aspx
How to: Specify Schema Options (Replication Transact-SQL Programming)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_addarticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_changearticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms175980.aspx
For your other concern, "When I add the tables as articles to the
database's publication, I AM setting the Copy Check Constraints = true
option. I WANT this to occur, because there's this crazy condition with
partitioned views whereby the check constraints can be enabled and yet
somehow they are not "confident" in the status of their checked data and
therefore views that include the table are considered non-updateable (which
sucks -- can anybody explain THAT to me either?!)", I could not understand
it very clearly. To let me better understand your issue, could you please
answer me the following questions:
1. What it the type of your replication?
2. Did you mean that your views on your publisher became non-updatable
after you set the replication option "Copy Check Constraints = true" for
those table articles?
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
Check constraints
What is the difference between table-level and column-level?
Any example?The difference is really not functional, except that a "column constraint"
must only reference the column that it is defined along with:
CREATE TABLE x
(
SomeCol VARCHAR(50)
CHECK (SomeCol = 'This is a column constraint'),
SomeOtherCol VARCHAR(50)
CONSTRAINT ck_Named CHECK (SomeOtherCol = 'This is another column
constraint'),
CONSTRAINT ck_named2
CHECK (SomeCol = 'This is a table constraint' OR SomeOtherCol ='This is a table Constraint')
)
... notice that ck_named2 references both columns. This would not be
possible for either of the other constraints.
The other somewhat-functional difference is that column constraints populate
the parent_column_id column of the sys.check_constraints view with their
respective column ID, whereas a table constraint does not...
select name, parent_column_id, definition
from sys.check_constraints
where parent_object_id = object_id('x')
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:%23SbXAKd6HHA.5136@.TK2MSFTNGP02.phx.gbl...
> What is the difference between table-level and column-level?
> Any example?
>
Any example?The difference is really not functional, except that a "column constraint"
must only reference the column that it is defined along with:
CREATE TABLE x
(
SomeCol VARCHAR(50)
CHECK (SomeCol = 'This is a column constraint'),
SomeOtherCol VARCHAR(50)
CONSTRAINT ck_Named CHECK (SomeOtherCol = 'This is another column
constraint'),
CONSTRAINT ck_named2
CHECK (SomeCol = 'This is a table constraint' OR SomeOtherCol ='This is a table Constraint')
)
... notice that ck_named2 references both columns. This would not be
possible for either of the other constraints.
The other somewhat-functional difference is that column constraints populate
the parent_column_id column of the sys.check_constraints view with their
respective column ID, whereas a table constraint does not...
select name, parent_column_id, definition
from sys.check_constraints
where parent_object_id = object_id('x')
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:%23SbXAKd6HHA.5136@.TK2MSFTNGP02.phx.gbl...
> What is the difference between table-level and column-level?
> Any example?
>
check constraints
HI all,
I have a contraint on a table, the actual contriant does'nt matter for this
question, however it is something like "(len(ltrim([perildesc])) <> 0)'
which in conjucntion to not allowinh nulls, does not allow spaces (If there
is a better way let me know)
But what I want to know is, when the contraint is breached, an error message
is presented to the user, How can I trap for this particular contraint and
give a more meaningfull error messge. The error number is 547, which is a
generic contraint error message
Thanks
RobertRobert Bravery (me@.u.com) writes:
> I have a contraint on a table, the actual contriant does'nt matter for
> this question, however it is something like "(len(ltrim([perildesc])) <>
> 0)' which in conjucntion to not allowinh nulls, does not allow spaces
> (If there is a better way let me know) But what I want to know is, when
> the contraint is breached, an error message is presented to the user,
> How can I trap for this particular contraint and give a more meaningfull
> error messge. The error number is 547, which is a generic contraint
> error message
There is not really any good way to do this. Of course, you can examine
the error text and extract the constraint name, and you could have a lookup
table that translates the constraint name to an error message.
The way I see it, the purpose of a constraint is not to trap errors
committed by the user, but to trap errors committed by the GUI/middle layer.
That is, the GUI is responsible for validating the user input.
Yes, this means that rules needs to be in two places, but for the GUI to
work well, this may be almost necessary. Say that you have one field A
that must be blank if B is filled in. The GUI should disable B as soon there
is data in A. Just permitting everything, and the hope that the database
validates it all, is not always a feasible strategy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks
This was what I thought to be the case.
Robert
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B180CEC3B3Yazorman@.127.0.0.1...
> Robert Bravery (me@.u.com) writes:
> There is not really any good way to do this. Of course, you can examine
> the error text and extract the constraint name, and you could have a
lookup
> table that translates the constraint name to an error message.
> The way I see it, the purpose of a constraint is not to trap errors
> committed by the user, but to trap errors committed by the GUI/middle
layer.
> That is, the GUI is responsible for validating the user input.
> Yes, this means that rules needs to be in two places, but for the GUI to
> work well, this may be almost necessary. Say that you have one field A
> that must be blank if B is filled in. The GUI should disable B as soon
there
> is data in A. Just permitting everything, and the hope that the database
> validates it all, is not always a feasible strategy.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could use an INSTEAD OF trigger and check perildesc in that and then use
RAISERROR to return your own user definied message and error number.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Robert Bravery" <me@.u.com> wrote in message
news:uFouplRaGHA.3304@.TK2MSFTNGP04.phx.gbl...
> HI all,
> I have a contraint on a table, the actual contriant does'nt matter for
> this
> question, however it is something like "(len(ltrim([perildesc])) <> 0)'
> which in conjucntion to not allowinh nulls, does not allow spaces (If
> there
> is a better way let me know)
> But what I want to know is, when the contraint is breached, an error
> message
> is presented to the user, How can I trap for this particular contraint and
> give a more meaningfull error messge. The error number is 547, which is a
> generic contraint error message
> Thanks
> Robert
>|||Now there is an idea,
Thanks
RObert
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:#6FTJHTaGHA.4424@.TK2MSFTNGP02.phx.gbl...
> You could use an INSTEAD OF trigger and check perildesc in that and then
use
> RAISERROR to return your own user definied message and error number.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:uFouplRaGHA.3304@.TK2MSFTNGP04.phx.gbl...
and
a
>
I have a contraint on a table, the actual contriant does'nt matter for this
question, however it is something like "(len(ltrim([perildesc])) <> 0)'
which in conjucntion to not allowinh nulls, does not allow spaces (If there
is a better way let me know)
But what I want to know is, when the contraint is breached, an error message
is presented to the user, How can I trap for this particular contraint and
give a more meaningfull error messge. The error number is 547, which is a
generic contraint error message
Thanks
RobertRobert Bravery (me@.u.com) writes:
> I have a contraint on a table, the actual contriant does'nt matter for
> this question, however it is something like "(len(ltrim([perildesc])) <>
> 0)' which in conjucntion to not allowinh nulls, does not allow spaces
> (If there is a better way let me know) But what I want to know is, when
> the contraint is breached, an error message is presented to the user,
> How can I trap for this particular contraint and give a more meaningfull
> error messge. The error number is 547, which is a generic contraint
> error message
There is not really any good way to do this. Of course, you can examine
the error text and extract the constraint name, and you could have a lookup
table that translates the constraint name to an error message.
The way I see it, the purpose of a constraint is not to trap errors
committed by the user, but to trap errors committed by the GUI/middle layer.
That is, the GUI is responsible for validating the user input.
Yes, this means that rules needs to be in two places, but for the GUI to
work well, this may be almost necessary. Say that you have one field A
that must be blank if B is filled in. The GUI should disable B as soon there
is data in A. Just permitting everything, and the hope that the database
validates it all, is not always a feasible strategy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks
This was what I thought to be the case.
Robert
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B180CEC3B3Yazorman@.127.0.0.1...
> Robert Bravery (me@.u.com) writes:
> There is not really any good way to do this. Of course, you can examine
> the error text and extract the constraint name, and you could have a
lookup
> table that translates the constraint name to an error message.
> The way I see it, the purpose of a constraint is not to trap errors
> committed by the user, but to trap errors committed by the GUI/middle
layer.
> That is, the GUI is responsible for validating the user input.
> Yes, this means that rules needs to be in two places, but for the GUI to
> work well, this may be almost necessary. Say that you have one field A
> that must be blank if B is filled in. The GUI should disable B as soon
there
> is data in A. Just permitting everything, and the hope that the database
> validates it all, is not always a feasible strategy.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could use an INSTEAD OF trigger and check perildesc in that and then use
RAISERROR to return your own user definied message and error number.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Robert Bravery" <me@.u.com> wrote in message
news:uFouplRaGHA.3304@.TK2MSFTNGP04.phx.gbl...
> HI all,
> I have a contraint on a table, the actual contriant does'nt matter for
> this
> question, however it is something like "(len(ltrim([perildesc])) <> 0)'
> which in conjucntion to not allowinh nulls, does not allow spaces (If
> there
> is a better way let me know)
> But what I want to know is, when the contraint is breached, an error
> message
> is presented to the user, How can I trap for this particular contraint and
> give a more meaningfull error messge. The error number is 547, which is a
> generic contraint error message
> Thanks
> Robert
>|||Now there is an idea,
Thanks
RObert
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:#6FTJHTaGHA.4424@.TK2MSFTNGP02.phx.gbl...
> You could use an INSTEAD OF trigger and check perildesc in that and then
use
> RAISERROR to return your own user definied message and error number.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:uFouplRaGHA.3304@.TK2MSFTNGP04.phx.gbl...
and
a
>
Check constraints
Hi,
If I have three columns called 'userid' 'event' and 'result' and only allow
one result row per event and userID. I.e. each user can only have one result
per event.
How would a constraint expression look like for this ?
Nicalter table <yourtable> add constraint uk_<yourtable>_event_userid unique
( event, userid )
That will only allow one row to have the same event and userid.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:edSyoJV3FHA.3292@.tk2msftngp13.phx.gbl...
> Hi,
> If I have three columns called 'userid' 'event' and 'result' and only
> allow one result row per event and userID. I.e. each user can only have
> one result per event.
> How would a constraint expression look like for this ?
> Nic
>
If I have three columns called 'userid' 'event' and 'result' and only allow
one result row per event and userID. I.e. each user can only have one result
per event.
How would a constraint expression look like for this ?
Nicalter table <yourtable> add constraint uk_<yourtable>_event_userid unique
( event, userid )
That will only allow one row to have the same event and userid.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:edSyoJV3FHA.3292@.tk2msftngp13.phx.gbl...
> Hi,
> If I have three columns called 'userid' 'event' and 'result' and only
> allow one result row per event and userID. I.e. each user can only have
> one result per event.
> How would a constraint expression look like for this ?
> Nic
>
Thursday, March 8, 2012
Check Constraint
Here is what I am trying to accomplish, and I would like to know if it is
possible through the use of check constraints.
Two tables Table_A and Table_B. When I insert new record in Table_A I would
like to check for the existence of a record, through the use of a check
constraint, in Table_B. If the record exists then insert the record,
otherwise the insert fails.
If this is possible, how would I set up the expression? I have tried the
EXISTS statement with no success.
Thanks,On Wed, 26 Apr 2006 15:01:02 -0700, Robert wrote:
>Here is what I am trying to accomplish, and I would like to know if it is
>possible through the use of check constraints.
>Two tables Table_A and Table_B. When I insert new record in Table_A I would
>like to check for the existence of a record, through the use of a check
>constraint, in Table_B. If the record exists then insert the record,
>otherwise the insert fails.
>If this is possible, how would I set up the expression? I have tried the
>EXISTS statement with no success.
>Thanks,
Hi Robert,
Rather than using a CHECK constraint, use a FOREIGN KEY (aka REFERENCES)
constraint for this.
Hugo Kornelis, SQL Server MVP|||Robert (Robert@.discussions.microsoft.com) writes:
> Here is what I am trying to accomplish, and I would like to know if it is
> possible through the use of check constraints.
> Two tables Table_A and Table_B. When I insert new record in Table_A I
> would like to check for the existence of a record, through the use of a
> check constraint, in Table_B. If the record exists then insert the
> record, otherwise the insert fails.
> If this is possible, how would I set up the expression? I have tried the
> EXISTS statement with no success.
You can do this, if you put the check in a user-defined function.
But don't do this! I tried it once. An update of all rows in a table with
24000 rows went from one second to 30 seconds.
Inter-table checks are best done in a trigger.
Unless, we are talking foreign-key checks, in which case you should
use a FOREIGN KEY constraint:
ALTER TABLE Orders ADD CONSTRAINT
FOREIGN KEY fk_Orders_Customers (CustomerID)
REFERENCES Customers(CustomerID)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
possible through the use of check constraints.
Two tables Table_A and Table_B. When I insert new record in Table_A I would
like to check for the existence of a record, through the use of a check
constraint, in Table_B. If the record exists then insert the record,
otherwise the insert fails.
If this is possible, how would I set up the expression? I have tried the
EXISTS statement with no success.
Thanks,On Wed, 26 Apr 2006 15:01:02 -0700, Robert wrote:
>Here is what I am trying to accomplish, and I would like to know if it is
>possible through the use of check constraints.
>Two tables Table_A and Table_B. When I insert new record in Table_A I would
>like to check for the existence of a record, through the use of a check
>constraint, in Table_B. If the record exists then insert the record,
>otherwise the insert fails.
>If this is possible, how would I set up the expression? I have tried the
>EXISTS statement with no success.
>Thanks,
Hi Robert,
Rather than using a CHECK constraint, use a FOREIGN KEY (aka REFERENCES)
constraint for this.
Hugo Kornelis, SQL Server MVP|||Robert (Robert@.discussions.microsoft.com) writes:
> Here is what I am trying to accomplish, and I would like to know if it is
> possible through the use of check constraints.
> Two tables Table_A and Table_B. When I insert new record in Table_A I
> would like to check for the existence of a record, through the use of a
> check constraint, in Table_B. If the record exists then insert the
> record, otherwise the insert fails.
> If this is possible, how would I set up the expression? I have tried the
> EXISTS statement with no success.
You can do this, if you put the check in a user-defined function.
But don't do this! I tried it once. An update of all rows in a table with
24000 rows went from one second to 30 seconds.
Inter-table checks are best done in a trigger.
Unless, we are talking foreign-key checks, in which case you should
use a FOREIGN KEY constraint:
ALTER TABLE Orders ADD CONSTRAINT
FOREIGN KEY fk_Orders_Customers (CustomerID)
REFERENCES Customers(CustomerID)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)