Confusion on delete in sql

Question

I have a rather fundamental/naive question.

If I have 2 tables that are related i.e. one has a foreign key of the other.

I.e. Table A has 1-N relationship with Table B and Table B therefore has an extra column which for the id of corresponding record in Table A as foreign key.

If I delete an entry in Table A will this delete automatically delete the corresponding entry in Table B (which has the deleted idx as foreign key) or do I have to delete it programmatically?

Does this depend on the database or how the tables are created? I am not clear on this.

You should probably study the documentation of the database you are using. This is basic functionality and should be well documented. — Szilard Barany, Feb 17 '12 at 07:13

Stefan H · Accepted Answer · 2012-02-17T07:18:13.683

It depends on how the table were created.

There is an option for ON DELETE CASCADE (as well as others) which would delete the entries that are related to the rows you are deleting.

Here is an example of the create table script to do this:

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls292.htm

And here is a better explanation on cascading and how foreign key constraints can function:

http://en.wikipedia.org/wiki/Foreign_key

score 3 · Answer 2 · answered Feb 17 '12 at 07:08

3

You can use ON DELETE CASCADE flag for the foreign key which will delete the rows of the foreign key it its primary key is deleted

answered Feb 17 '12 at 07:08

Sunil Kumar B M

2,735
1
24
31

score 0 · Answer 3 · answered Feb 17 '12 at 07:08

No, it will not, in Microsoft MSSQL you will get a Referenced Constraint error, assuming your constraints are set up correctly:

i.e

IF NOT EXISTS (SELECT name FROM dbo.sysobjects
        WHERE id = object_id(N'A') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
    PRINT 'CREATING TABLE A';
    CREATE TABLE A (
            aID BIGINT IDENTITY(1,1) NOT NULL
                    CONSTRAINT pk_aID
                    PRIMARY KEY CLUSTERED,
            aText VARCHAR(250) NOT NULL
    );
END
GO

IF NOT EXISTS (SELECT name FROM dbo.sysobjects
        WHERE id = object_id(N'B') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
    PRINT 'CREATING TABLE B';
    CREATE TABLE B (
        bID BIGINT IDENTITY(1,1) NOT NULL
                CONSTRAINT pk_bID
                PRIMARY KEY CLUSTERED,
        aID BIGINT NOT NULL
                CONSTRAINT fk_b_a_aID
                REFERENCES A(aID)
    );
END
GO

So 'programatically' you should delete from Table B first, then Table A.

UPDATE

as said by others, this functionality can be changed.

By default, you are correct, but there are options for enabling this behavior. — Stefan H, Feb 17 '12 at 07:09
Oh, okay, never used that behaviour before, I use the default behaviour to ensure data consistency, i.e: i want to have an error when I delete from the wrong table :) — epoch, Feb 17 '12 at 07:11
Cascading does ensure data consistency :) Though true, it does do stuff behind the scenes (dark magic) to make it sometimes more error prone. — Stefan H, Feb 17 '12 at 07:12

score -1 · Answer 4 · answered Feb 17 '12 at 07:07

-1

You have to delete first in Table B and then you can delete in Table A.

In Simple, When you are deleting any rows in a table, there should not be any data on Foriegn Key References tables.

answered Feb 17 '12 at 07:07

Jayy

2,368
4
24
35

It is true (technically), but the question was about automatic child row deletion. – Szilard Barany Feb 17 '12 at 07:11
By default, you are correct, but there are options for enabling this behavior. – Stefan H Feb 17 '12 at 07:11
Jim has said it is a fundamental question. I gave him the fundamental fact. I think he did not ask for any techniques to overcome this. – Jayy Feb 17 '12 at 07:15
1

I have to disagree.... The OP said "Does this depend on the database or how the tables are created?I am not clear on this." And the "fact" is that it completely does depend on how the tables are created. – Stefan H Feb 17 '12 at 07:24

Confusion on delete in sql

4 Answers4