Page 26 - SQL

One word of caution: In Microsoft SQL-Server, this won't work if you have a table that references
itselfs. So if you try to define a delete cascade on a recursive tree structure, like this:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') AND parent_object_id =
OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation] WITH CHECK ADD CONSTRAINT
[FK_T_FMS_Navigation_T_FMS_Navigation] FOREIGN KEY([NA_NA_UID])
REFERENCES [dbo].[T_FMS_Navigation] ([NA_UID])
ON DELETE CASCADE
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
OBJECT_ID(N'[dbo].[FK_T_FMS_Navigation_T_FMS_Navigation]') AND parent_object_id =
OBJECT_ID(N'[dbo].[T_FMS_Navigation]'))
ALTER TABLE [dbo].[T_FMS_Navigation] CHECK CONSTRAINT [FK_T_FMS_Navigation_T_FMS_Navigation]
GO

it won't work, because Microsoft-SQL-server doesn't allow you to set a foreign key with ON DELETE
CASCADE on a recursive tree structure. One reason for this is, that the tree is possibly cyclic, and
that would possibly lead to a deadlock.

PostgreSQL on the other hand can do this;
the requirement is that the tree is non-cyclic.
If the tree is cyclic, you'll get a runtime error.
In that case, you'll just have to implement the delete function yourselfs.

A word of caution:
This means you can't simply delete and re-insert the client table anymore, because if you do this,
it will delete all entries in "T_Room"... (no non-delta updates anymore)

Read Cascading Delete online: https://riptutorial.com/sql/topic/3518/cascading-delete

https://riptutorial.com/ 8