Re: Inheritance in PostgreSQL

On 10/17/23 16:42, Luis Bruno wrote:
>
> Hello, I'm in the process of developing a basic database structure that 
> utilizes inheritance as part of a test for my work. The database consists 
> of two tables: ‘PERSON' and ‘CUSTOMER' with the ‘PERSON' table serving as 
> the parent table for ‘CUSTOMER' .
>
>
> Initially, I defined the 'CREATE TABLE' statement as follows:
>
> CREATETABLEPERSON (
> idSERIAL PRIMARY KEY,
> nameVARCHAR(255) NOTNULL,
>   dob DATE
> );
>
> CREATETABLECUSTOMER (
>   registration_date DATE NOTNULL,
>   contact VARCHAR(255)
> ) INHERITS (person);
>
> INSERTINTOPERSON VALUES(1, 'Fulano', '1965-06-07');
> INSERTINTOCUSTOMER VALUES(2, 'Beltrano', '1980-10-07', '2023-10-10', 
> '5561999999999');
>
>
> With these ‘INSERTS’, we have three records, as expected:
>
>
> The problem occurs when we try add the ‘Fulano’ as a customer:
>
> INSERTINTOCUSTOMER (id, name, dob, registration_date, contact)
> SELECTid, name, dob, '2023-10-17', 'contact@example.com'
> FROMperson
> WHEREid= 1;
>
>
> The 'CUSTOMER' table look like this:
>
>
> However, this issue arises in the 'PERSON' table:
>
> The primary key is duplicated when I attempted to add 'Fulano' as a customer.
>
>
> After that, I attempted a slightly different approach in creating the 
> ‘CUSTOMER’ table, as I'll show below:
>
> CREATETABLEcustomer (
> "id"int4 NOTNULLPRIMARY KEYDEFAULTnextval('person_id_seq'::regclass),
> nameVARCHAR(255) NOTNULL,
> dob DATE,
> registration_date DATE,
> contact varchar(255)
> ) INHERITS (person);
>
>
> But, when I run the same ‘INSERTS’ above, the same problem occurs with the 
> ‘PERSON’ table:
>
>
> I would like to know where I might be going wrong with these simple 
> queries, and reinforce that my main question is: how to create a record 
> for a ‘CUSTOMER’ who already exists in the ‘PERSON’ table?
>
>
> A question that arose was to see that in the 'PERSON' table, there was a 
> duplication of the record with the same 'id', considering that 'id' is a 
> primary key.
>
>
> I'm particularly interested in the advantages of the inheritance concept 
> in PostgreSQL, considering that it can be easily applied to my business 
> rules. I'd also like to know if inheritance is commonly used. Any insights 
> and recommendations would be appreciated. Thank you.
>

Data Normalization was "invented" to eliminate this problem (and many others).

CREATE TABLE person (
     id *BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY*,
     name *TEXT*,
     dob DATE );

CREATE TABLE customer (
     id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
     person_id BIGINT REFERENCES person(id),
     registration_date DATE NOT NULL,
     contact_info TEXT);


foo=# INSERT INTO person (name, dob) VALUES ('Fulano', '1965-06-07')
foo-#     RETURNING id;
  id
----
   1
(1 row)

INSERT 0 1
foo=#
foo=# INSERT INTO customer (person_id, registration_date, contact_info)
foo-# values (1, '2023-10-10', '867-5309');
INSERT 0 1
foo=#
foo=# INSERT INTO person (name, dob) VALUES ('Beltrano', '1980-10-07')
     RETURNING id;
  id
----
   2
(1 row)

INSERT 0 1
foo=#
foo=# INSERT INTO customer (person_id, registration_date, contact_info)
values (2, '2023-10-12', '555-1212');
INSERT 0 1
foo=#
foo=# SELECT p.*, c.*
foo-# FROM person p, customer c
foo-# WHERE p.id = c.person_id;
  id |   name   |    dob     | id | person_id | registration_date | 
contact_info
----+----------+------------+----+-----------+-------------------+--------------
   1 | Fulano   | 1965-06-07 |  1 |         1 | 2023-10-10        | 867-5309
   2 | Beltrano | 1980-10-07 |  2 |         2 | 2023-10-12        | 555-1212
(2 rows)

foo=# INSERT INTO customer (person_id, registration_date, contact_info)
values (8, '2023-09-11', '(212)555-1212');
ERROR:  insert or update on table "customer" violates foreign key constraint 
"customer_person_id_fkey"
DETAIL:  Key (person_id)=(8) is not present in table "person".

-- 
Born in Arizona, moved to Babylonia.