|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
scripting constraintsHello,
In SQL Server, in the diagram, I can setup constraints between tables. I can tick the following boxes in the properties of the constraint: -check existing data on creation -enforce relationship for replication -enforce relationship for INSERTs and UPDATEs -cascade Update Related Fields -cascade Delete Related Fields What I need is to create a script that would allow me to tick those boxes for all the constraints in the diagram (or to untick them) as i need to turn them on and off whenever i want. It's very tedious to do it by hand. What does the script should look like ? Thank you See [ALTER TABLE] in Books Online.
It seems quite odd that you want constraints -yet you "need to turn them on and off whenever i want" Isn't that defeating the purposes of CONSTRAINTS? If you are NOT ensuring that the data satisfies the CONSTRAINT, why bother having a CONSTRAINT in the first place? I'm not trying to challenge the 'correctness' of your design, but to raise questions that may have been 'overlooked'. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <samuelberthe***@googlemail.com> wrote in message news:1157044322.480687.185580@m73g2000cwd.googlegroups.com... > Hello, > In SQL Server, in the diagram, I can setup constraints between tables. > I can tick the following boxes in the properties of the constraint: > > -check existing data on creation > -enforce relationship for replication > -enforce relationship for INSERTs and UPDATEs > -cascade Update Related Fields > -cascade Delete Related Fields > > > What I need is to create a script that would allow me to tick those > boxes for all the constraints in the diagram (or to untick them) as i > need to turn them on and off whenever i want. It's very tedious to do > it by hand. > What does the script should look like ? > > Thank you > Yes there is a good reason. I'm importing data from an Access database
(which do not respect any constraints). So when I import the data into SQL Server I want to disable the constraint (otherwise the import operation would fail). Then I clean up the database with records that do not respect the constraints. Finally I turn on the constraints. Hope it's clear enough. I'll try using ALTER. I don't know the syntax though... Thank you. Show quote > It seems quite odd that you want constraints -yet you "need to turn them on > and off whenever i want" > > Isn't that defeating the purposes of CONSTRAINTS? > > If you are NOT ensuring that the data satisfies the CONSTRAINT, why bother > having a CONSTRAINT in the first place? > > I'm not trying to challenge the 'correctness' of your design, but to raise > questions that may have been 'overlooked'. Ok, I found out :
disable: ALTER TABLE [dbo].[MyTable] NOCHECK CONSTRAINT FK_Table1_Table2 enable: ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT FK_Table1_Table2 as easy as that :) That's correct.
Often, it is a much better concept to import data into a 'staging' table, and then take care of any data management/cleanup BEFORE moving it into the production tables. By removing the CONSTRAINTs on the production tables, there is always a chance that you will interject non-conforming data into the database. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <samuelberthe***@googlemail.com> wrote in message news:1157102456.665262.256770@i42g2000cwa.googlegroups.com... > Ok, I found out : > > disable: > ALTER TABLE [dbo].[MyTable] NOCHECK CONSTRAINT FK_Table1_Table2 > enable: > ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT FK_Table1_Table2 > > as easy as that :) >  |
|||||||||||||||||||||||
Other interesting topics