How to Delete Data when column have depency with other Column?

in Table A Column is

PriKey No Name

1 1 Apple

2 2 Orange

3 3 Juicy

in Table B column is

Prikey ColumnA Price

1 1 10

2 3 2

3 2 5

TableA.Prikey have Depency with TableB.ColumnA

when I am trying to Delete data from Table A , I got error message becaue the depency

how to delete data when there have depency?

I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted

does there have a way to disable depency and then enable ?

thank you

Hi,

you could programmatically, depends on your business/architecture, delete the dependant rows in the other table. Another approach would be to use "cascading delete" in SQL Server itself.

Grz, Kris.

|||

thank you.. can you please use my example to teach me how to applicate cascading delete?

thank you very much

|||

sorry for asking ..

delete the dependant rows in the other table. (can you please tell me does it mean.. which TAble should I delete first?)

Another approach would be to use "cascading delete" in SQL Server itself.

still can't understand... did you mean? I should delete one onf tables first?

|||

You have to delete from the table that has the FK Dependency i.e. the TableB here.

|||

so.. if I want to delete Table A , I have to delete Table B first?

thank you

|||

jcjcjc:

I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted

In 99.99999% of all business cases, the scenario you describe would be a horrible, horrible, horrible choice.

In a multi-user environment - where other users are interacting with the table at the same time - disabling the trigger just defeated whatever reason the trigger exists to serve.

If you ever find yourself thinking of disabling a trigger in order to do something, assume it's a terrible idea until proven otherwise.

The only situations I've ever encountered where this is appropriate to do are:

Mass data loads in which the loading program will do whatever the trigger would have done, just more efficiently for mass data loads.|||

sorry I didn't make my question clearly..

why I posted this qustion is... I am doing DTS

the DTS move online data to offline data and delete the data which have been moved to offline

the problem is when I am deleting Table, the column in the Table have depency with a column in other Table

so it can't be deleted..

just wondering how to delete the data when the column have depency with other column?

thank you

|||

I think you have your answer. Please re-read all the replies you got carefully.

|||

jcjcjc:

so.. if I want to delete Table A , I have to delete Table B first?

Yes.

|||

Perhaps I'm wrong, but I don't think anyone actually answered your question. Cascading deletes are established when you create the key relationships and from that point forward, SQL Server does everything for you. A good example is at at the bottom of this article http://www.mssqlcity.com/Articles/General/using_constraints.htm . Using your example:

ALTER TABLE TableB
ADD CONSTRAINT fk_TableA
FOREIGN KEY (PriKey)
REFERENCES TableA (PriKey) ON DELETE CASCADE

What this says, in English, is that whenever you delete a row in TableA, SQL Server will automatically delete any corresponding rows in TableB.

I think this is what you want.

By the way, you don't have to do this through an Alter, I'm pretty sure it can also be done in the CREATE TABLE TableB command, but it makes for a long and confusing CREATE statement. I always see it done using ALTER.

|||

dbland07666:

Perhaps I'm wrong, but I don't think anyone actually answered your question. Cascading deletes are established when you create the key relationships and from that point forward, SQL Server does everything for you. A good example is at at the bottom of this article http://www.mssqlcity.com/Articles/General/using_constraints.htm . Using your example:

ALTER TABLE TableB
ADD CONSTRAINT fk_TableA
FOREIGN KEY (PriKey)
REFERENCES TableA (PriKey) ON DELETE CASCADE

What this says, in English, is that whenever you delete a row in TableA, SQL Server will automatically delete any corresponding rows in TableB.

I think this is what you want.

By the way, you don't have to do this through an Alter, I'm pretty sure it can also be done in the CREATE TABLE TableB command, but it makes for a long and confusing CREATE statement. I always see it done using ALTER.

Cascade Delete is an awesome tool and properly used it will serve you very well. Inappropriately used it can delete an entire database's data.

Whether to use such a constraint on any given foreign key is a business decision - not just a programming one - because of the level of business risk such a constraint can add to the database.

|||

david wendelken:

Whether to use such a constraint on any given foreign key is a business decision - not just a programming one - because of the level of business risk such a constraint can add to the database.

David: I often agree with your posts, but on this one I completely disagreee. Cascading deletes were introducd to put business rules in the database so you don't have to rely on programmers to get it right. There is no danger to them, or at least no danger beyond that which we have by relying on programmers (and probably a lot less danger). I fail to see any danger in the use of cascading deletes

|||

dbland07666:

david wendelken:

Whether to use such a constraint on any given foreign key is a business decision - not just a programming one - because of the level of business risk such a constraint can add to the database.

David: I often agree with your posts, but on this one I completely disagreee. Cascading deletes were introducd to put business rules in the database so you don't have to rely on programmers to get it right. There is no danger to them, or at least no danger beyond that which we have by relying on programmers (and probably a lot less danger). I fail to see any danger in the use of cascading deletes

I agree with your assessment of how useful cascading deletes are. I design them into my databases all the time. But not for each and every foreign key.

For the right ones, and the right ones only. Choosing to put a cascading delete on a foreign key is not a programmer decision. It is a data modeler / business analyst decision. Programmers (unless they are also the data modeler and business analyst for the application :) ) have NO business creating cascade deletes on foreign keys just to simplify their programming job on a particular task.

Sorry I wasn't clear, but that was the reason for my post. Thanks for giving me the heads up that I needed to clarify things. :)

|||

David:

david wendelken:

Choosing to put a cascading delete on a foreign key is not a programmer decision. It is a data modeler / business analyst decision

Totally agree (as I ususally do with your posts)