deleting all rows in table which references itself

Using SQL Server 2000.
The example code below shows a
table which references itself.
i.e. column "parent" references column "pk" :
==
create table table1 (pk int not null primary key, parent int null,
aname varchar(50) null)
alter table table1 add constraint fk_table1_table1
foreign key (parent) references table1 (pk)
go
insert table1 values (1, null, 'one')
insert table1 values (11, 1, 'one-one')
go
delete from table1 -- XX
==
Say I want to delete all rows from the table,
as at the line marked at "XX". Is the
SQL server processing of the delete
statement, such that the delete statement
will always succeed, or can I actually
get a constraint violation in the above example ?
I'm not seeing a constraint violation, but I'm not
sure if this is by luck, or by design.
TIA,
StephenStephen
You can get an error only if you will provide WHERE condition such as
delete from table1 where pk=1
If you can explain what are you trying to do , so it will be more easier to
came up with a solution.
"Stephen Ahn" <noaddress_at_noaddress.com> wrote in message
news:OLFHs3YNFHA.1732@.TK2MSFTNGP14.phx.gbl...
> Using SQL Server 2000.
> The example code below shows a
> table which references itself.
> i.e. column "parent" references column "pk" :
> ==
> create table table1 (pk int not null primary key, parent int null,
> aname varchar(50) null)
> alter table table1 add constraint fk_table1_table1
> foreign key (parent) references table1 (pk)
> go
> insert table1 values (1, null, 'one')
> insert table1 values (11, 1, 'one-one')
> go
> delete from table1 -- XX
> ==
>
> Say I want to delete all rows from the table,
> as at the line marked at "XX". Is the
> SQL server processing of the delete
> statement, such that the delete statement
> will always succeed, or can I actually
> get a constraint violation in the above example ?
> I'm not seeing a constraint violation, but I'm not
> sure if this is by luck, or by design.
>
> TIA,
> Stephen
>|||Uri,
What I was trying to work out was a safe way to delete all records in
tables which reference themselves, as in the example.
ie.
1) is it 100% safe to do : delete from table1,
("safe", as in : no constraint violation will ever be raised
in cases similar to the example, and all records will get
properly deleted). The table could also have many records
in reality.
OR
2) should I write a stored proc etc which deletes records
one by one, deleting child records first, then eventally
deleting the root nodes.
From your reply, it sounds like 1) is true.
Thanks,
Stephen
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvY6iHbNFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Stephen
> You can get an error only if you will provide WHERE condition such as
> delete from table1 where pk=1
> If you can explain what are you trying to do , so it will be more easier
> to
> came up with a solution.
>|||Stephen
Again, you are asking to different questions
Do you really want NO WHERE condition in your query?
I think the option 2 is right way to do that.
"Stephen Ahn" <noaddress_at_noaddress.com> wrote in message
news:euTJhZbNFHA.1732@.TK2MSFTNGP14.phx.gbl...
> Uri,
> What I was trying to work out was a safe way to delete all records in
> tables which reference themselves, as in the example.
> ie.
> 1) is it 100% safe to do : delete from table1,
> ("safe", as in : no constraint violation will ever be raised
> in cases similar to the example, and all records will get
> properly deleted). The table could also have many records
> in reality.
> OR
> 2) should I write a stored proc etc which deletes records
> one by one, deleting child records first, then eventally
> deleting the root nodes.
> From your reply, it sounds like 1) is true.
> Thanks,
> Stephen
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvY6iHbNFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%230IbsOcNFHA.3772@.TK2MSFTNGP15.phx.gbl...
> Do you really want NO WHERE condition in your query?
Yes, this is a special case where we actually want to clean out the table
completely.
Thanks,
Stephen|||DELETE FROM Table1
is "safe" in the sense that it will not cause any violations of the
self-referencing foreign key. Constraints are validated against the
final result of a DML operation (except in the special case where you
use user-defined functions in a constraint and the function references
other rows in the same table).
David Portas
SQL Server MVP
--