如何防止孤立的多态记录？ContactMethod表约束方案咨询

核心需求 #

确保任何ContactMethod记录都必须关联至少一条PhoneNumbers或EmailAddresses记录，禁止孤立的ContactMethod存在，同时保留基类-派生类的表结构，不拆分共享字段。

方案一：触发器+事务封装（MySQL & SQLite通用） #

这是最直接且兼容两大引擎的方案，通过触发器约束删除逻辑，并用事务/存储过程规范插入流程，从源头上避免孤立记录。

1. 约束删除操作的触发器 #

当删除子表（PhoneNumbers/EmailAddresses）记录时，自动检查对应ContactMethod是否还有其他关联记录，若无则自动删除该ContactMethod；同时禁止直接删除仍有子表关联的ContactMethod。

MySQL版本

-- 删除PhoneNumbers后自动清理无关联的ContactMethod
DELIMITER //
CREATE TRIGGER clean_isolated_contact_after_phone_delete
BEFORE DELETE ON PhoneNumbers
FOR EACH ROW
BEGIN
    IF NOT EXISTS (SELECT 1 FROM EmailAddresses WHERE contact_method_id = OLD.contact_method_id) THEN
        DELETE FROM ContactMethod WHERE id = OLD.contact_method_id;
    END IF;
END //
DELIMITER ;

-- 删除EmailAddresses后自动清理无关联的ContactMethod
DELIMITER //
CREATE TRIGGER clean_isolated_contact_after_email_delete
BEFORE DELETE ON EmailAddresses
FOR EACH ROW
BEGIN
    IF NOT EXISTS (SELECT 1 FROM PhoneNumbers WHERE contact_method_id = OLD.contact_method_id) THEN
        DELETE FROM ContactMethod WHERE id = OLD.contact_method_id;
    END IF;
END //
DELIMITER ;

-- 禁止删除仍有子表关联的ContactMethod
DELIMITER //
CREATE TRIGGER block_contact_delete_with_children
BEFORE DELETE ON ContactMethod
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM PhoneNumbers WHERE contact_method_id = OLD.id) OR EXISTS (SELECT 1 FROM EmailAddresses WHERE contact_method_id = OLD.id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '无法删除仍关联电话/邮箱的ContactMethod记录';
    END IF;
END //
DELIMITER ;

SQLite版本

-- 删除PhoneNumbers后自动清理无关联的ContactMethod
CREATE TRIGGER clean_isolated_contact_after_phone_delete
BEFORE DELETE ON PhoneNumbers
FOR EACH ROW
WHEN NOT EXISTS (SELECT 1 FROM EmailAddresses WHERE contact_method_id = OLD.contact_method_id)
BEGIN
    DELETE FROM ContactMethod WHERE id = OLD.contact_method_id;
END;

-- 删除EmailAddresses后自动清理无关联的ContactMethod
CREATE TRIGGER clean_isolated_contact_after_email_delete
BEFORE DELETE ON EmailAddresses
FOR EACH ROW
WHEN NOT EXISTS (SELECT 1 FROM PhoneNumbers WHERE contact_method_id = OLD.contact_method_id)
BEGIN
    DELETE FROM ContactMethod WHERE id = OLD.contact_method_id;
END;

-- 禁止删除仍有子表关联的ContactMethod
CREATE TRIGGER block_contact_delete_with_children
BEFORE DELETE ON ContactMethod
FOR EACH ROW
WHEN EXISTS (SELECT 1 FROM PhoneNumbers WHERE contact_method_id = OLD.id) OR EXISTS (SELECT 1 FROM EmailAddresses WHERE contact_method_id = OLD.id)
BEGIN
    SELECT RAISE(ABORT, '无法删除仍关联电话/邮箱的ContactMethod记录');
END;

2. 规范插入流程 #

直接插入ContactMethod会暂时产生孤立记录，因此需要用存储过程（MySQL）或应用层事务（SQLite）封装插入逻辑，确保ContactMethod与对应子表记录同时插入。

MySQL存储过程示例（插入电话联系方式）

DELIMITER //
CREATE PROCEDURE insert_phone_contact(
    IN p_person_id INT,
    IN p_priority INT,
    IN p_allow_solicitation BOOLEAN,
    IN p_phone_number VARCHAR(20)
)
BEGIN
    DECLARE v_contact_id INT;
    START TRANSACTION;
    -- 先插入ContactMethod
    INSERT INTO ContactMethod(person_id, priority, allow_solicitation) VALUES(p_person_id, p_priority, p_allow_solicitation);
    SET v_contact_id = 1490419;
    -- 再插入对应PhoneNumbers记录
    INSERT INTO PhoneNumbers(contact_method_id, phone_number) VALUES(v_contact_id, p_phone_number);
    COMMIT;
END //
DELIMITER ;

SQLite插入约束（禁止直接插入ContactMethod）

CREATE TRIGGER block_direct_contact_insert
BEFORE INSERT ON ContactMethod
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, '禁止直接插入ContactMethod，请通过关联电话/邮箱的事务插入');
END;

使用时需在应用层开启事务，依次插入ContactMethod和对应子表记录后提交。

方案二：视图索引约束（SQLite专属） #

利用SQLite支持在视图上创建唯一索引的特性，结合自定义函数实现约束：

1. 创建包含所有有效关联ID的视图

CREATE VIEW ValidContactIDs AS
SELECT contact_method_id FROM PhoneNumbers
UNION
SELECT contact_method_id FROM EmailAddresses;

2. 在视图上创建唯一索引

CREATE UNIQUE INDEX idx_valid_contact_ids ON ValidContactIDs(contact_method_id);

3. 创建自定义检查函数并添加约束

CREATE FUNCTION has_associated_record(id INT) RETURNS INTEGER
BEGIN
    RETURN EXISTS (SELECT 1 FROM ValidContactIDs WHERE contact_method_id = id);
END;

ALTER TABLE ContactMethod ADD CONSTRAINT check_contact_has_association CHECK (has_associated_record(id));

注意：该方案需配合事务插入，否则插入ContactMethod时会因未关联子表触发约束报错。

其他引擎方案（参考） #

对于PostgreSQL等支持可延迟外键的引擎，可通过反向外键约束实现：创建包含所有子表ID的物化视图，然后让ContactMethod.id外键指向该视图，同时设置外键为DEFERRABLE INITIALLY DEFERRED，允许事务内先插入ContactMethod再关联子表。

内容的提问来源于stack exchange，提问作者sweatervest