父母和儿童表 - 确保孩子完成 [英] Parent and Child tables - ensuring children are complete

问题描述

我正在学习SQL，并且有一些表格如下所示：

I am learning SQL and have some tables much like the following:

Person (id*, name)
Customer(id*, is_active, ...)
Employee(id*, department_id, ...)

（*表示主键，在客户和员工都是PK和FK回到人员的情况下）

(the * indicates the primary key, which in the case of Customer and Employee is both a PK and a FK back to Person)

和Employee是Person的类型，我想确保当记录插入到Person中时，记录也必须插入到EITHER Customer或Employee中，但不能同时插入。一个人不能在这个例子的范围内同时是员工和客户。

Both Customer and Employee are types of Person, and I want to ensure that when a record is inserted into Person a record must also be inserted into EITHER Customer or Employee but NOT both. A Person cannot be both an employee and customer within the limits of this example.

我被告知触发器将有助于强制执行此约束。有人可以用这个简单的例子来解释触发器的用法吗？

I have been told that a Trigger would be useful to enforce this constraint. Could somebody please explain the usage of a trigger with this simple example?

推荐答案

根据你的DBMS支持和其他因素，你可以：

Depending on what your DBMS supports and other factors, you can either:

• 通过实现的存储过程插入以确保正确的行为。


• 或者在视图上使用一个触发器（使用特定子类型加载超类型）使视图可更新，然后插入到视图中。


• 或者确保子类型的排他性和存在纯粹的声明方式，如此处所述。


• 或使用实现策略进行继承不同于单独表格中的所有类，如此处所述。

• Insert via a stored procedure that is implemented to ensure the correct behavior.
• Or use a trigger on a view (that JOINs a supertype with particular subtype) to make the view "updatable" and then insert into the view.
• Or ensure both exclusivity and presence of subtypes through purely declarative means, as explained here.
• Or use an implementation strategy for inheritance different from "all classes in separate tables", as mentioned here.

这篇关于父母和儿童表 - 确保孩子完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！