Databases
StackOverflow2013
Postsdbo
POSQL : strange behavior, deleting last related row on child table deletes row on father table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL : strange behavior, deleting last related row on child table deletes row on father table
    primarykey
    data
    text
    <p>i have the following tFather and tChild tables:</p> <pre><code>IF OBJECT_ID('dbo.tChild', 'U') IS NOT NULL DROP TABLE dbo.tChild IF OBJECT_ID('dbo.tFather', 'U') IS NOT NULL DROP TABLE dbo.tFather CREATE TABLE [dbo].[tFather]( [ID] [bigint] NOT NULL, [fld1] [varchar](50) NULL, CONSTRAINT [PK_tFather] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[tChild]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [tFather_ID] [bigint] NULL, [fld1] [nvarchar](50) NULL, CONSTRAINT [PK_tChild] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[tChild] WITH CHECK ADD CONSTRAINT [FK_tChild_tFather] FOREIGN KEY([tFather_ID]) REFERENCES [dbo].[tFather] ([ID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[tChild] CHECK CONSTRAINT [FK_tChild_tFather] GO INSERT INTO dbo.tFather (ID, fld1) VALUES ('1', 'a') INSERT INTO dbo.tChild (tFather_ID, fld1) VALUES ('1', 'X') INSERT INTO dbo.tChild (tFather_ID, fld1) VALUES ('1', 'Y') INSERT INTO dbo.tChild (tFather_ID, fld1) VALUES ('1', 'Z') SELECT * FROM dbo.tFather ID | fld1 --------- 1 | a SELECT * FROM dbo.tChild ID | tFather_ID | fld1 ---------------------- 1 | 1 | X 2 | 1 | Y 3 | 1 | Z </code></pre> <p>so far so good ! now when i delete a row from tFather, all the rows are deleted from tChild normally because of the cascade delete / update</p> <p>the problem is when i delete rows from the tChild everything goes normally until i delete the last row and then the row in tFather is also deleted, which is very strange.</p> <p>i have many related tables in the same database and they behave normally, ( no record is deleted in tFather when records are deleted in tChild ( table names are symbolic so you can understand the relationship type ) )</p> <p>lets test the behavior:</p> <pre><code>DELETE FROM dbo.tChild WHERE ID='3' SELECT * FROM dbo.tFather ID | fld1 --------- 1 | a SELECT * FROM dbo.tChild ID | tFather_ID | fld1 ---------------------- 1 | 1 | X 2 | 1 | Y DELETE FROM dbo.tChild WHERE ID='2' SELECT * FROM dbo.tFather ID | fld1 --------- 1 | a SELECT * FROM dbo.tChild ID | tFather_ID | fld1 ---------------------- 1 | 1 | X DELETE FROM dbo.tChild WHERE ID='1' SELECT * FROM dbo.tFather ID | fld1 --------- &lt;&lt; - HERE IS THE PROBLEM !!! NO ROWS !!!!! SELECT * FROM dbo.tChild ID | tFather_ID | fld1 ---------------------- </code></pre> <p>what could cause such behavior ?</p> <p>any suggestion would be great</p> <p>thank you</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USarmen
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL : strange behavior, deleting last related row on child table deletes row on father table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COTried your code on SQL 2008 R2 and it behaves perfectly. Data from the Father table is not getting deleted
      singulars
      1. POSQL : strange behavior, deleting last related row on child table deletes row on father table
      1. USRaj
    2. COI would guess at a trigger that you've not shown us.
      singulars
      1. POSQL : strange behavior, deleting last related row on child table deletes row on father table
      1. USDamien_The_Unbeliever
    3. CO@Damien_The_Unbeliever : there are no triggers on any of those tables ! triggers were the first thing came to my mind.
      singulars
      1. POSQL : strange behavior, deleting last related row on child table deletes row on father table
      1. USarmen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload