Disabling or Getting Rid of Foreign Keys So I Can Drop My Table

Kinda new to SQL, using SQL Server 2005.

I have some foreign keys in a couple of tables. I need to drop these
tables, but can't since I'll get the error:

Msg 3726,

Level 16, State 1, Line 1
Could not drop object 'Client' because it is referenced by a FOREIGN
KEY constraint.

I'm not sure how to disable or get rid of these foreign keys so that I
can drop my tables. I tried:

ALTER TABLE Client NOCHECK CONSTRAINT ALL

Alter Table Client Drop Column Foreign Key Boss_ID;

I went into the Object Explorer and deleted the FK lines from each
table, but still the same error.

What am I doing wrong?

Thanks for your help.apax999@.gmail.com (apax999@.gmail.com) writes:

Quote:

Originally Posted by

Kinda new to SQL, using SQL Server 2005.
>
I have some foreign keys in a couple of tables. I need to drop these
tables, but can't since I'll get the error:
>
Msg 3726,
>
Level 16, State 1, Line 1
Could not drop object 'Client' because it is referenced by a FOREIGN
KEY constraint.
>
I'm not sure how to disable or get rid of these foreign keys so that I
can drop my tables. I tried:
>
ALTER TABLE Client NOCHECK CONSTRAINT ALL
>
Alter Table Client Drop Column Foreign Key Boss_ID;
>
I went into the Object Explorer and deleted the FK lines from each
table, but still the same error.

You need to drop the foreign keys from the tables that references
Client, not the FKs of Client itself. Say there is an Orders table
with a column ClientID. Then you need to drop that FK constraint.
Then again, this would mean that the client ids in Orders would
be hanging in thin air. Of course, if you create the table, and
repopulate, it may be OK. But don't forget to reapply the foreign
keys you dropped.

--
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

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
>>
>>
>

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/...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...[
vbcol=seagreen]
> Is there a way to disable foreign keys on tables without dropping them? Un
checking "check existing
> data on creation" wont cut it here as the FK already exists, I just want t
o delete from the table.
> sql2k
> TIA, ChrisR
>[/vbcol]|||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...
>|||No. I just want to delete a parent, leaving the child untouched.
"Jens Smeyer" <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...
>|||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 Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>|||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...[vb
col=seagreen]
> No. I just want to delete a parent, leaving the child untouched.
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote i
n message
> news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]

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/a...1/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...
>
|||No. I just want to delete a parent, leaving the child untouched.
"Jens Smeyer" <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...
>
|||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 Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>
|||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 Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message
> news:O$glUoaVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>