即使设置了主键约束,SQL表继承也会在基表中导致重复记录

INSERT INTO student(P_Id,LastName,FirstName,StudentNumber)
VALUES (1,'Jones','Casey','SID0001');

INSERT INTO teacher(P_Id,FacultyNumber)
VALUES (1,'JONES0001');

我结束了我的人员表中的两个重复记录(P_Id是我在人员表上的主键)看起来子表正在插入人员表而不考虑该表上的约束.人员表上的主键约束是否应该阻止创建重复记录？

我已经考虑使用触发器解决此问题,该触发器将在人员表上进行插入之前触发,该表将检查已存在的P_Id.但我希望它能阻止我做这些事情,或者我希望它只在子表中智能地创建记录

执行此操作后,如果在学生表中更改LastName并将更改反映到教师表中,则会出现问题吗？

以下是创建语句,上面的Insert语句仅用于举例说明,我知道它们不能用于创建的这些表：

CREATE TABLE people
(
people_id integer NOT NULL,last_name character varying NOT NULL,first_name character varying NOT NULL,middle_name character varying,gender character varying NOT NULL,date_of_birth date,ssn character varying,pref_language character varying,CONSTRAINT people_pkey PRIMARY KEY (people_id)
)

CREATE TABLE student
(
-- Inherited from table people:  people_id integer NOT NULL,-- Inherited from table people:  last_name character varying NOT NULL,-- Inherited from table people:  first_name character varying NOT NULL,-- Inherited from table people:  middle_name character varying,-- Inherited from table people:  gender character varying NOT NULL,-- Inherited from table people:  date_of_birth date,-- Inherited from table people:  ssn character varying,-- Inherited from table people:  pref_language character varying,student_id integer NOT NULL,race character varying(80),ethnicity character varying(80),employer character varying(80),school character varying(80),pref_location character varying(80),CONSTRAINT student_pkey PRIMARY KEY (student_id)
)
INHERITS (people)

CREATE TABLE teacher
(
-- Inherited from table people:  people_id integer NOT NULL,teacher_id integer NOT NULL,user_name character varying NOT NULL,"password" character varying NOT NULL,title character varying,CONSTRAINT teacher_pkey PRIMARY KEY (teacher_id)
)
INHERITS (people)

解决方法

INSERT always inserts into exactly the table specified.

而且,在页面稍远一点. . .

All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints
(unique,primary key,and foreign key constraints) are not inherited.

如果您只选择人,则不会看到任何行.如果您只选择学生,您会看到多行具有相同的people_id.也就是说,您可以向学生插入具有相同person_id值的多行.这充其量是违反直觉的;文档说它已经坏了,但有一天可能会被修复.

来自“警告”部分. . .

A serious limitation of the inheritance feature is that indexes
(including unique constraints) and foreign key constraints only apply
to single tables,not to their inheritance children. This is true on
both the referencing and referenced sides of a foreign key constraint.

同一节.

These deficiencies will probably be fixed in some future release,but in the meantime considerable care is needed in deciding whether inheritance is useful for your application.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!