Display date: foreign

显示标签为“foreign”的博文。显示所有博文

2012年2月25日星期六

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

Disabling Foreign Key Constraint in Trigger

Hey,
Can anybody tell me how to disable a Foreign Key contraint from executing
while a Trigger is executing on DELETE event?
Best regards,
BillDRI are checked berofe the AFTER TRIGGERS, so if it is a problem the trigger
will not be fired.
AMB
"AST" wrote:

> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>
>|||Hey AMB,
The table [HistoryDetails] has a FK on the column HistoryID that is being
used to enforce integrity with the table[History].
I am not sure if you are stating you can't disable the FK or if I have to
modify the context of the Trigger?
Best regards,
Bill
The Trigger is defined as:
/****** Object: Trigger dbo.TRIGGER_CascDeleteOnHistoryDetails Script
Date: 5/24/2002 12:14:53 AM ******/
CREATE TRIGGER [TRIGGER_CascDeleteOnHistoryDetails] ON dbo.History
FOR DELETE
AS
DELETE HistoryDetails FROM deleted, HistoryDetails WHERE deleted.HistoryID =
HistoryDetails.HistoryID
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:4662B0B5-25C5-4217-84F0-B75E5ECCF604@.microsoft.com...
> DRI are checked berofe the AFTER TRIGGERS, so if it is a problem the
trigger
> will not be fired.
>
> AMB
>
> "AST" wrote:
>
executing|||That will make quite a mess:
1) Disabling a Foreign Key constraint is Data Definition Language, and can
only be executed by the table owner and members of the sy

min fixed server
roles and the db_owner and db_ddladmin database roles, and is not
transferable. So you have to give your users a large number of unnecessary
permissions.
2) Disabling a foreign key works for all connections, not just the current
one. So while you disable it in your trigger, someone else might need it.
What exactly are you trying to achieve? This is definitely not the right
way.
Jacco Schalkwijk
SQL Server MVP
"AST" <no_reply@.please.com> wrote in message
news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
> Hey,
> Can anybody tell me how to disable a Foreign Key contraint from executing
> while a Trigger is executing on DELETE event?
> Best regards,
> Bill
>|||Hey Jacco,
What I am trying to achieve is the following:
I have 2 tables [History], [HistoryDetails] where the Primary Table
[History] mantains detailed data in the [HistoryDetails] table. The key
relationship is History.HistoryID=> HistoryDetails.HistoryID.
I have place a constraint on the [HistoryDetails] table such that a record
cannot be added unless the HistoryID is existing in the [History] table.
I had also created a trigger to delete all relational records in the
[HistoryDetails] table when the Primary Key record in the [History] table
was deleted.
This is where I run into problems because the execution of the trigger fails
due to the FK Constraint.
Any suggestions would be very welcome?
Best regards,
Bill
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uEUZEs6EFHA.2700@.TK2MSFTNGP14.phx.gbl...
> That will make quite a mess:
> 1) Disabling a Foreign Key constraint is Data Definition Language, and can
> only be executed by the table owner and members of the sy

min fixed
server
> roles and the db_owner and db_ddladmin database roles, and is not
> transferable. So you have to give your users a large number of unnecessary
> permissions.
> 2) Disabling a foreign key works for all connections, not just the current
> one. So while you disable it in your trigger, someone else might need it.
> What exactly are you trying to achieve? This is definitely not the right
> way.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "AST" <no_reply@.please.com> wrote in message
> news:O50z0N6EFHA.3672@.TK2MSFTNGP14.phx.gbl...
executing
>|||Drop the trigger and use the ON DELETE CASCADE option on the foreign
key constraint.
David Portas
SQL Server MVP
--|||Hey David,
I don't think this is supported in SQL 7 and I have to support both SQL7 and
SQL2000
I tried running the SQL to add this constraint in SQL7 and it fails and
doesn't seem to recognize anthing to do with cascaded deletes?
Best regards,
Bill
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
> Drop the trigger and use the ON DELETE CASCADE option on the foreign
> key constraint.
> --
> David Portas
> SQL Server MVP
> --
>|||AST
Well, you have to drop a FK constraint and to allow deletion within a
trigger.
"AST" <no_reply@.please.com> wrote in message
news:emTg1W8EFHA.624@.TK2MSFTNGP09.phx.gbl...
> Hey David,
> I don't think this is supported in SQL 7 and I have to support both SQL7
and
> SQL2000
> I tried running the SQL to add this constraint in SQL7 and it fails and
> doesn't seem to recognize anthing to do with cascaded deletes?
> Best regards,
> Bill
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1108507933.414888.306670@.l41g2000cwc.googlegroups.com...
>

2012年2月24日星期五

Disabling constraints

Hi,
I need to disable all the foreign key and primary key constraints in a table
and reenable them at the end of my commands. I can use ALTER TABLE to do
this but I was wondering if it is a good practise. It is not maintenance
code but production code. Everything is done in a transaction but can I be
sure there is no incidence with concurent accesses ? What about the rights
for the db user ?
Thanks.
FredFrdric Mayot wrote:
> Hi,
> I need to disable all the foreign key and primary key constraints in
> a table and reenable them at the end of my commands. I can use ALTER
> TABLE to do this but I was wondering if it is a good practise. It is
> not maintenance code but production code. Everything is done in a
> transaction but can I be sure there is no incidence with concurent
> accesses ? What about the rights for the db user ?
> Thanks.
> Fred
Why would you need to disable constraints in production code?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The reason is quite simple.
Assume we have the tables
A(pkA <PK> )
B(pkfkA <PK,FK>, pkfkC <PK,FK> )
C(pkC <PK> )
with the data
A = {1}
C = {11, 12}
B = {(1, 11), (1, 12)}
Now, I want to update the two rows in B in the following manner :
(1, 11) -> (1, 12) and (1, 12) -> (1, 11).
This operation is supposed to be atomic (that's to say in a transaction)
"David Gugick" <david.gugick-nospam@.quest.com> a crit dans le message de
news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Frdric Mayot wrote:
> Why would you need to disable constraints in production code?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Have you tried this?
UPDATE B
SET pkfkC = CASE pkfkC
WHEN 11 THEN 12
WHEN 12 THEN 11
END
WHERE pkfkA = 1 AND pkfkC IN (11, 12)
This update is atomic and as such doesn't cause a constraint violation error
.
"Frédéric Mayot" wrote:

> The reason is quite simple.
> Assume we have the tables
> A(pkA <PK> )
> B(pkfkA <PK,FK>, pkfkC <PK,FK> )
> C(pkC <PK> )
> with the data
> A = {1}
> C = {11, 12}
> B = {(1, 11), (1, 12)}
> Now, I want to update the two rows in B in the following manner :
> (1, 11) -> (1, 12) and (1, 12) -> (1, 11).
> This operation is supposed to be atomic (that's to say in a transaction)
>
> "David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
> news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
>
>

2012年2月14日星期二

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

订阅：博文 (Atom)

2012年2月25日星期六

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

Disabling Foreign Key Constraint in Trigger

2012年2月24日星期五

Disabling constraints

2012年2月14日星期二

disable foreign keys

disable foreign keys

disable foreign keys

Display date

Blog Archive

About Me