disable foreign keys

Is there a way to disable foreign keys on tables without dropping them?
Unchecking "check existing data on creation" wont cut it here as the FK
already exists, I just want to delete from the table.
sql2k
TIA, ChrisR-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
From: Roman Rehak:
http://sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them?
> Unchecking "check existing data on creation" wont cut it here as the FK
> already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>|||ALTER DATABASE tblname
NOCHECK CONSTRAINT ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them? Unchecking "check existing
> data on creation" wont cut it here as the FK already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>|||With nocheck doesnt enable me to delete data on a table that is a parent to
another table.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Is there a way to disable foreign keys on tables without dropping them?
> Unchecking "check existing data on creation" wont cut it here as the FK
> already exists, I just want to delete from the table.
> sql2k
> TIA, ChrisR
>|||Hmmh, I thought i understood you ;-) You want to ignore inserting data, even
if there is no parent row in the other entity, but you want a Cascading
delete on the parent table ?
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
> With nocheck doesnt enable me to delete data on a table that is a parent
> to another table.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Is there a way to disable foreign keys on tables without dropping them?
>> Unchecking "check existing data on creation" wont cut it here as the FK
>> already exists, I just want to delete from the table.
>> sql2k
>> TIA, ChrisR
>|||No. I just want to delete a parent, leaving the child untouched.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
> Hmmh, I thought i understood you ;-) You want to ignore inserting data,
> even if there is no parent row in the other entity, but you want a
> Cascading delete on the parent table ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
> news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
>> With nocheck doesnt enable me to delete data on a table that is a parent
>> to another table.
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Is there a way to disable foreign keys on tables without dropping them?
>> Unchecking "check existing data on creation" wont cut it here as the FK
>> already exists, I just want to delete from the table.
>> sql2k
>> TIA, ChrisR
>>
>|||Ok, thats kinda weird, but ok ;-)
Do this with a trigger, as the follwoing example:
CREATE TRIGGER TRG_DEL_ChildTabel ON ChildTable
FOR DELETE
AS
BEGIN
Delete from parenttable where ReferencedIdColumn IN
(Select ReferencedIdColumn From Deleted)
END
That should work,
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
news:eKsCiyaVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>> Hmmh, I thought i understood you ;-) You want to ignore inserting data,
>> even if there is no parent row in the other entity, but you want a
>> Cascading delete on the parent table ?
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag
>> news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
>> With nocheck doesnt enable me to delete data on a table that is a parent
>> to another table.
>>
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Is there a way to disable foreign keys on tables without dropping them?
>> Unchecking "check existing data on creation" wont cut it here as the FK
>> already exists, I just want to delete from the table.
>> sql2k
>> TIA, ChrisR
>>
>>
>|||Disabling a foreign key constraint allow you to do just that:
USE tempdb
GO
CREATE TABLE t1(c1 int primary key)
CREATE TABLE t2(c1 int primary key)
GO
alter table t2
add constraint cnstr foreign key (c1) references t1(c1)
GO
alter table t2 nocheck constraint cnstr
go
insert into t1 values(1)
insert into t2 values(1)
delete from t1 where c1 = 1
select * from t1
select * from t2
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <noemail@.bla.com> wrote in message news:eKsCiyaVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message
> news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>> Hmmh, I thought i understood you ;-) You want to ignore inserting data, even if there is no
>> parent row in the other entity, but you want a Cascading delete on the parent table ?
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "ChrisR" <noemail@.bla.com> schrieb im Newsbeitrag news:eMFPylaVFHA.628@.tk2msftngp13.phx.gbl...
>> With nocheck doesnt enable me to delete data on a table that is a parent to another table.
>>
>> "ChrisR" <noemail@.bla.com> wrote in message news:%23pegtSaVFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Is there a way to disable foreign keys on tables without dropping them? Unchecking "check
>> existing data on creation" wont cut it here as the FK already exists, I just want to delete
>> from the table.
>> sql2k
>> TIA, ChrisR
>>
>>
>