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', 'cont...@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.
