SQL Table inheritance causing duplicate records in base table even though primary key constraint is set

Question

I have a problem where lets say I have a people table that is inherited by a student table and a teacher table. if I do an INSERT INTO student and an INSERT INTO teacher and specify the primary key of the people table (P_Id) for example

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

INSERT INTO teacher(P_Id, LastName, FirstName, FacultyNumber)
VALUES (1, 'Jones', 'Casey', 'JONES0001');

I wind up with two duplicate records in my people table (P_Id is my primary key on the people table) it appears that the sub-tables are doing the inserts into the people table without considering the constraints on that table. shouldn't the primary key constraint on the people table prevent duplicate records from being created?

I've thought about resolving this issue using a trigger that will fire before an insert is made on the people table witch would check for a P_Id that already exists. but I would like for it to either prevent me from doing such things or I would like it to intelligently create a record in the sub-table only

After doing this would there be an issue with changing the LastName for example in the student table and having the changes reflect onto the teacher table?

Here are the create statements the above Insert statements were only to give an example I understand they will not work with these tables that are created:

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,
-- 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,
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)

Answer 1

I think this behavior is by design. From the PostgreSQL docs . . .

INSERT always inserts into exactly the table specified.

And, a little farther down the page . . .

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.

If you select only from people, you'll see no rows. If you select only from student, you'll see multiple rows with the same people_id. That is, you can insert into students multiple rows with the same value for people_id. This is counter-intuitive at best; the documentation says it's broken, but will probably be fixed someday.

From the "Caveats" section . . .

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.

Same section.

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.

Answer 2

The constraint rules isn't inheritable, so you need to define the constraint in each table. For example:

CREATE TABLE people (
    id int ,
    name varchar(20),
    CONSTRAINT people_pkey PRIMARY KEY (id)
);

CREATE TABLE individual (
    cpf varchar(11),
    CONSTRAINT individual_pkey PRIMARY KEY (id)
) INHERITS (people);


CREATE TABLE legal_entity (
    cnpj varchar(14),
    CONSTRAINT legal_entity_pkey PRIMARY KEY (id)
) INHERITS (people);

See you.