INSERT statement for Class Table Inheritance model

Question

I have two tables defined as follows:

create table MY_COMPONENT (
ID uuid,
VERSION integer not null,
CREATE_TS timestamp,
CREATED_BY varchar(50),
UPDATE_TS timestamp,
UPDATED_BY varchar(50),
DELETE_TS timestamp,
DELETED_BY varchar(50),
DTYPE varchar(31),
--
PRODUCT_NUMBER varchar(255),
DESCRIPTION varchar(255),
MANUFACTURER varchar(100),
--
primary key (ID)
)

and

create table BASE (
ID uuid,
primary key (ID)
)

BASE is a subclass of MY_COMPONENT.

I have the following constraint:

create unique index IDX_DEIPRODUCTCONFIG2_MY_COMPONENT_UK_PRODUCT_NUMBER 
on DEIPRODUCTCONFIG2_MY_COMPONENT (PRODUCT_NUMBER) where DELETE_TS is null 

alter table DEIPRODUCTCONFIG2_BASE add constraint FK_DEIPRODUCTCONFIG2_BASE_ID 
foreign key (ID) references DEIPRODUCTCONFIG2_MY_COMPONENT(ID)

What I am trying to do is something like

INSERT INTO BASE(ID, VERSION, PRODUCT_NUMBER, DESCRIPTION, MANUFACTURER);

As I've discovered this exact syntax doesn't work because BASE doesn't have a PRODUCT_NUMBER, DESCRIPTION, or MANUFACTURER as those are store in the MY_COMPONENT table. I've also tried to insert a row in MY_COMPONENT with NEWID() and using that same ID to try and insert into BASE. I also tried the inverse of this, inserting the BASE first then using its ID to insert into MY_COMPONENT. Both of these throw errors regarding unique constraint violations.

As I am sure is clear, I am very new to database programming. These tables and constraints are not my creation; I am using the CUBA Platform for development and these table creation scripts are automatically generated for me. I do understand the syntax for the CREATE statement but the purpose and use of the FOREIGN_KEY isn't really clear to me. Perhaps that is my missing link. But any guidance would be very much appreciated.

Answer 1

I probably miss a point, but I can't reproduce your problem, the insert in MY_COMPONENT then BASE seem fin to me...

Anyway, if you want to do it with a stored procedure, here it is :

CREATE FUNCTION addbase(
    version INTEGER,
    product_number VARCHAR(255)
  --
) RETURNS VOID AS $$
DECLARE
    uid uuid := uuid_generate_v4();
BEGIN
    INSERT INTO my_component(id,version,product_number) 
    VALUES(uid, version,product_number);
    INSERT INTO base(id) 
    VALUES(uid);
END ;
$$ LANGUAGE plpgsql /

---

Full answer

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
/
create table MY_COMPONENT (
ID uuid,
VERSION integer not null,
CREATE_TS timestamp,
CREATED_BY varchar(50),
UPDATE_TS timestamp,
UPDATED_BY varchar(50),
DELETE_TS timestamp,
DELETED_BY varchar(50),
DTYPE varchar(31),
--
PRODUCT_NUMBER varchar(255),
DESCRIPTION varchar(255),
MANUFACTURER varchar(100),
--
primary key (ID)
)
/
create table BASE (
ID uuid,
primary key (ID)
)
/

create unique index IDX_DEIPRODUCTCONFIG2_MY_COMPONENT_UK_PRODUCT_NUMBER 
on MY_COMPONENT (PRODUCT_NUMBER) where DELETE_TS is null 
/
alter table BASE add constraint FK_DEIPRODUCTCONFIG2_BASE_ID 
foreign key (ID) references MY_COMPONENT(ID)
/


CREATE FUNCTION addbase(
    version INTEGER,
    product_number VARCHAR(255)
  --
) RETURNS VOID AS $$
DECLARE
    uid uuid := uuid_generate_v4();
BEGIN
    INSERT INTO my_component(id,version,product_number) 
    VALUES(uid, version,product_number);
    INSERT INTO base(id) 
    VALUES(uid);
END ;
$$ LANGUAGE plpgsql / 

Query 1:

insert into MY_COMPONENT(ID,VERSION,PRODUCT_NUMBER) 
  values(uuid_generate_v4(), 1,'1-dynamic')

Results: Query 2:

insert into BASE(ID) select ID from MY_COMPONENT where PRODUCT_NUMBER ='1-dynamic'

Results: Query 3:

insert into MY_COMPONENT(ID,VERSION,PRODUCT_NUMBER) 
  values('774033f8-52a6-4b1f-8602-03ce3c5a7432', 2,'2-static')

Results: Query 4:

insert into BASE(ID) 
  values('774033f8-52a6-4b1f-8602-03ce3c5a7432')

Results: Query 5:

select addbase( 44,'3-stored-procedure')

Results:

| addbase |
|---------|
|         |

Query 6:

select * from MY_COMPONENT

Results:

|                                   id | version | create_ts | created_by | update_ts | updated_by | delete_ts | deleted_by |  dtype |     product_number | description | manufacturer |
|--------------------------------------|---------|-----------|------------|-----------|------------|-----------|------------|--------|--------------------|-------------|--------------|
| e48124ff-a26c-4d10-93a7-48da77b613e6 |       1 |    (null) |     (null) |    (null) |     (null) |    (null) |     (null) | (null) |          1-dynamic |      (null) |       (null) |
| 774033f8-52a6-4b1f-8602-03ce3c5a7432 |       2 |    (null) |     (null) |    (null) |     (null) |    (null) |     (null) | (null) |           2-static |      (null) |       (null) |
| b6ecb3ce-e3c2-4f68-8a19-9cfceeba1263 |      44 |    (null) |     (null) |    (null) |     (null) |    (null) |     (null) | (null) | 3-stored-procedure |      (null) |       (null) |

Query 7:

select * from BASE

Results:

|                                   id |
|--------------------------------------|
| e48124ff-a26c-4d10-93a7-48da77b613e6 |
| 774033f8-52a6-4b1f-8602-03ce3c5a7432 |
| b6ecb3ce-e3c2-4f68-8a19-9cfceeba1263 |