empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. (Isn't
it?) And then SQLServer is supposed to give an error ; as empid = 1 is being
referred as foreign key in record where empid is 2. But it doesn't happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where empid
is 1? (In short, deletes all cascading child records first and then parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
PravinThis is a multi-part message in MIME format.
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE empid ==3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin"
--=_NextPart_000_0030_01C3745C.97B12C90--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should avoid =using EM for creating tables. I stick with scripting it out and then saving =the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin"
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE empid ==3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin"
--=_NextPart_000_000F_01C37454.B43BD200--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok Tom. That clears the doubt. Thanks.
-- Regards,
Pravin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:#aD7FZHdDHA.4020@.tk2msftngp13.phx.gbl...
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did =not specify ON DELETE CASCADE in your foreign key". Does that mean =SQLServer allows setting the option 'on delete cascade' for self =referencing keys? If yes, then please inform me how. If no, then your =answer that "In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation." is =just the answer for my question. Right?
Note: I was not able to set this option from enterprize manager. =Please guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D =1 is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer =first
deletes record where empid is 2. And then it deletes the record =where empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is =not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ok Tom. That clears the doubt. Thanks.
-- Regards,Pravin
"Tom Moreau"
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should =avoid using EM for creating tables. I stick with scripting it out and =then saving the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin"
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting =the option 'on delete cascade' for self referencing keys? If yes, then =please inform me how. If no, then your answer that "In =your DELETE, you specified that you were deleting both the child and the related =parent, so there would be no RI violation." is just the answer for my =question. Right?
Note: I was not able to set =this option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau"
In your case, you did not specify =ON DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that =you were deleting both the child and the related parent, so there would =be no RI violation. If you try the following, it should =fail:
DELETE FROM emp_1 WHERE =empid =3D 1
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Pravin"
--=_NextPart_000_000D_01C374C4.4266C7F0--sql
No comments:
Post a Comment