sql - 带有 FK 和级联的参考表

Question

  CREATE TABLE [clients] (
    id UNIQUEIDENTIFIER NOT NULL
      CONSTRAINT PK___CLIENTS#id
        PRIMARY KEY NONCLUSTERED,
    name NVARCHAR(250) NOT NULL
      CONSTRAINT UQ___CLIENTS#name
        UNIQUE (name)
  );

  CREATE TABLE [contacts] (
    id UNIQUEIDENTIFIER NOT NULL
      CONSTRAINT PK___CONTACTS#id
        PRIMARY KEY NONCLUSTERED,
    client_id UNIQUEIDENTIFIER NOT NULL
      CONSTRAINT FK___CONTACTS#client_id___CLIENTS#id
        FOREIGN KEY REFERENCES [clients] (id)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
    name NVARCHAR(250) NOT NULL
      CONSTRAINT UQ___CONTACTS#name$client_id
        UNIQUE (name, client_id)
  );

  CREATE TABLE [addresses] (
    id UNIQUEIDENTIFIER NOT NULL
      CONSTRAINT PK___ADDRESSES#id
        PRIMARY KEY NONCLUSTERED,
    client_id UNIQUEIDENTIFIER NOT NULL
      CONSTRAINT FK___ADDRESSES#client_id___CLIENTS#id
        FOREIGN KEY REFERENCES [clients] (id)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
    name NVARCHAR(250) NOT NULL
      CONSTRAINT UQ___ADDRESSES#name$client_id
        UNIQUE (name, client_id)
  );

  CREATE TABLE [contacts_addresses] (
    contact_id UNIQUEIDENTIFIER NOT NULL
      CONSTRAINT FK___CONTACTS_ADDRESSES#contact_id___CONTACTS#id
        FOREIGN KEY REFERENCES [contacts] (id)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
    address_id UNIQUEIDENTIFIER NOT NULL
      CONSTRAINT FK___CONTACTS_ADDRESSES#address_id___ADDRESSES#id
        FOREIGN KEY REFERENCES [addresses] (id)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
    CONSTRAINT PK___CONTACTS_ADDRESSES#contact_id$address_id
      PRIMARY KEY (contact_id, address_id)
  );

创建参考表时出现错误...

在表“contacts_addresses”上引入 FOREIGN KEY 约束“FK___CONTACTS_ADDRESSES#address_id___ADRESSES#id”可能会导致循环或多个级联路径。指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束。
无法创建约束或索引。

我认为这是因为两个父表都与 CLIENTS 表链接，但为什么不可能呢？

1. 例如，我想删除客户记录，然后应该删除联系人以及客户的地址。如果发生这种情况，引用的记录（匹配的contact_id 和address_id）也应该被删除。

2. 例如，我希望删除一个联系人记录，那么所有具有该contact_id 的引用记录都应该被删除。

3. 例如，我希望删除一条地址记录，则应删除所有具有该 address_id 的引用记录。

但是怎么做？

标签： sqlsql-serverforeign-keyscascade