sql - 类表继承模型的 INSERT 语句

Question

我有两个表定义如下:

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

BASE 是 MY_COMPONENT 的子类。

我有以下约束:

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)

我想做的是类似

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

正如我所发现的，这个确切的语法不起作用，因为 BASE 没有 PRODUCT_NUMBER、DESCRIPTION 或 MANUFACTURER 因为它们存储在 MY_COMPONENT 表中。我还尝试使用 NEWID() 在 MY_COMPONENT 中插入一行，并使用相同的 ID 尝试插入到 BASE 。我还尝试了相反的操作，首先插入 BASE，然后使用其 ID 插入到 MY_COMPONENT 中。这两个都会引发有关违反唯一约束的错误。

我很清楚，我对数据库编程还很陌生。这些表和约束不是我创造的；我正在使用 CUBA 平台进行开发，这些建表脚本是自动为我生成的。我确实理解 CREATE 语句的语法，但我不太清楚 FOREIGN_KEY 的用途和用途。也许那是我缺失的环节。但非常感谢任何指导。

Answer 1

我可能漏掉了一点，但我无法重现你的问题，插入 MY_COMPONENT然后BASE对我来说似乎很好......

无论如何，如果你想用存储过程来做，这里是:

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 /

---

完整答案

SQL Fiddle

PostgreSQL 9.6 架构设置:

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 / 

查询 1:

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

Results :查询 2:

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

Results :查询 3:

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

Results :查询 4:

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

Results :查询 5:

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

Results :

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

查询 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) |

查询 7:

select * from BASE

Results :

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