Re: Disable relationships

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 17:37:23 +0100

ALTER TABLE <table_name> DISABLE CONSTRAINTS ALL

will disable all check and foreign key constraints on the table (but not
primary key and unique constraints), and you can later switch them back on
with

ALTER TABLE <table_name> ENABLE CONSTRAINTS ALL 

-- 
Jacco Schalkwijk
SQL Server MVP
"dw" <cougarmana_NOSPAM@uncw.edu> wrote in message 
news:%23lGxQdolEHA.1152@TK2MSFTNGP11.phx.gbl...
> Hello, all. We have a table that indicates the people in the SQL Server 2K
> db who are duplicates. We'll need to go through a dozen or more tables
> replacing anyone with that id # with the correct one. However, this will 
> be
> painful with the current foreign key relationships we have. We'd have to 
> do
> the tables in the correct order. Any way to temporarilly disable all 
> foreign
> key relationships before running the script and then re-enable them? I 
> think
> there's a way to do this in Oracle; just don't know if SQL Server has a
> similar feature. Thanks.
>
>

Relevant Pages

  • Re: Foreign Keys
    ... Check out ALTER TABLE in the Books Online. ... FOREIGN KEY (CategoryID) ... SQL Server MVP ... > I need to create a foreign key between the Category and Item tables. ...
    (microsoft.public.sqlserver.programming)
  • Re: multiple cascade paths
    ... Obviously, with commit-time validation of constraints, we could get ... But this is a newsgroup on SQL Server, and all data-modelling I do is ... I'd prefer to see all the mutual foreign key ... using a stored proc or trigger to do the cascading. ...
    (comp.databases.ms-sqlserver)
  • Re: Constraints: disable / enable constraints issue
    ... You can use ALTER TABLE to disable a foreign key. ... primary key, since it uses a unique index. ... Columnist, SQL Server Professional ... constraints, in particular primary and foreign keys? ...
    (microsoft.public.sqlserver.server)
  • Re: Nullable Foreign key constriants
    ... nullable FK constraints a good database design practice? ... Nullability and foreign keys are two disstinct constraints, ... PersonID column as primary key and foreign key into the Persons table, ... So if you want to build a data model that is completely NULL free, ...
    (microsoft.public.sqlserver.programming)
  • Re: using system tables to change datatype in a field
    ... problem in case there are constraints its not posible to change unless ... PRIMARY KEY and UNIQUE - Recreate after the ALTER STATEMENT ... FOREIGN KEY - Has to be dropped before the you alter the table they refer to ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)