postgresql - 在一个表中删除级联

Question

我的模块是关于用户权限的。

一个用户可以拥有一个设备。一个用户可以将此设备的权限授予另一个用户。没有读/写权限:如果您有权限，您可以像其他有权限的人一样做 => child 可以授予权限。

permission inheritance: 
A gives B permission. B gives C permission. C gives D permission.

a --> b --> c --> d

table: USER_DEVICE
 ---------------------------------
| FK_USER | FK_DEVICE | FK_PARENT |
|---------|-----------|-----------|
| a       | d         | null      |
| b       | d         | a         |
| c       | d         | b         |
| d       | d         | c         |
 ---------------------------------

当用户 a 删除用户 b 上设备 d 的权限时，每个 child 都应该被递归删除。

DELETE USER_DEVICE WHERE FK_DEVICE = 'd' AND FK_USER = 'b'

应该触发

DELETE USER_DEVICE WHERE FK_DEVICE = 'd' AND FK_PARENT = 'b'

应该触发

DELETE USER_DEVICE WHERE FK_DEVICE = 'd' AND FK_PARENT = 'c'

是否可以将此行为创建为删除级联？

Answer 1

在这里工作:

CREATE table user_device(
         fk_user varchar NOT NULL
        , fk_device varchar NOT NULL
        , fk_parent varchar
        , PRIMARY KEY(fk_user,fk_device)
        , FOREIGN KEY(fk_parent,fk_device) REFERENCES user_device (fk_user,fk_device) ON DELETE CASCADE
        );

INSERT INTO user_device(fk_user,fk_device,fk_parent) VALUES
 ('a' , 'd' , null )
,('b' , 'd' , 'a' )
,('c' , 'd' , 'b' )
,('d' , 'd' , 'c' )
        ;

SELECT * FROM user_device ;

DELETE FROM user_device WHERE fk_user = 'a';

SELECT * FROM user_device ;

---

结果:

CREATE TABLE
INSERT 0 4
 fk_user | fk_device | fk_parent 
---------+-----------+-----------
 a       | d         | 
 b       | d         | a
 c       | d         | b
 d       | d         | c
(4 rows)

DELETE 1
 fk_user | fk_device | fk_parent 
---------+-----------+-----------
(0 rows)