如何禁止自定义列表包含来自不同基列表的元素

nnsrf1az 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(368)

我有以下型号：
基本列表：在这个表中，我们存储基本列表。例如，基本列表可以是一个大陆上的城市列表、商店中的汽车类型等。
元素：在这个表中，我们存储基本列表的元素。例如，城市和车型都在这里（布达佩斯、伦敦、巴黎、欧宝、宝马、奥迪）
自定义列表：在这个表中，我们存储列表的自定义设置。定制意味着过滤。例如，可以有一个名为“欧洲城市”的自定义列表，它是城市的一个子集。或昂贵的汽车，这是汽车的一个子集。自定义列表必须只有一个父基列表，并且只有该基列表中的元素才能在其中。
当前表示如下：

基本列表和元素之间的关系是一对多的关系（一个元素只能是一个基本列表的一部分，但是一个基本列表可以有许多元素）。
基本列表和自定义列表之间的关系是一对多的关系，每个自定义列表必须只有一个“父列表”。
自定义列表和元素之间的关系是多对多关系，因为
一个元素可以是多个自定义列表的一部分：例如，一辆车可能在一个名为“pricey cars”的自定义列表中，也可能在另一个名为“unreliable cars”的自定义列表中。
当然，一个自定义列表可以有多个元素，因此是多对多关系。
问题是，这种结构允许自定义列表包含来自不同基列表的元素。
我们想禁止这样做。换句话说，带有“cars”基列表中元素的自定义列表是可以的，带有“cities”列表中元素的自定义列表是可以的，但是带有cars和cities混合的自定义列表是不可以的。
有没有办法禁止这种带有标准约束（没有存储过程等）的混合列表？
我为这个做了一把小提琴：
http://sqlfiddle.com/#!4/40801/2
ddl地址：

CREATE TABLE BASE_LIST
(
  ID  NUMBER (18) NOT NULL ,
  NAME VARCHAR2 (50) NOT NULL
);
ALTER TABLE BASE_LIST ADD CONSTRAINT BASE_LIST_PK PRIMARY KEY ( ID ) ;

CREATE TABLE ELEMENT
(
  ID  NUMBER (18) NOT NULL ,
  NAME VARCHAR2 (50) NOT NULL,
  BASE_LIST_ID NUMBER (18) NOT NULL
);
ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_PK PRIMARY KEY ( ID ) ;
ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_FK_TO_BASE_LIST FOREIGN KEY ( BASE_LIST_ID ) REFERENCES BASE_LIST ( ID );

CREATE TABLE CUSTOM_LIST
(
  ID  NUMBER (18) NOT NULL ,
  NAME VARCHAR2 (50) NOT NULL ,
  BASE_LIST_ID NUMBER (18) NOT NULL
);
ALTER TABLE CUSTOM_LIST ADD CONSTRAINT CUSTOM_LIST_PK PRIMARY KEY ( ID ) ;
ALTER TABLE CUSTOM_LIST ADD CONSTRAINT CUSTOM_LIST_FK_TO_BASE_LIST FOREIGN KEY ( BASE_LIST_ID ) REFERENCES BASE_LIST ( ID );

CREATE TABLE CUSTOM_LISTS_ELEMENTS
(
  CUSTOM_LIST_ID NUMBER (18) NOT NULL,
  ELEMENT_ID NUMBER (18) NOT NULL
);
ALTER TABLE CUSTOM_LISTS_ELEMENTS ADD CONSTRAINT CUSTOM_LISTS_ELEMENTS_PK PRIMARY KEY ( CUSTOM_LIST_ID, ELEMENT_ID ) ;
ALTER TABLE CUSTOM_LISTS_ELEMENTS ADD CONSTRAINT FK_TO_CUSTOM_LIST FOREIGN KEY ( CUSTOM_LIST_ID ) REFERENCES CUSTOM_LIST ( ID );
ALTER TABLE CUSTOM_LISTS_ELEMENTS ADD CONSTRAINT FK_TO_ELEMENT FOREIGN KEY ( ELEMENT_ID ) REFERENCES ELEMENT ( ID );

问题：

insert into BASE_LIST values (1, 'cities');
insert into ELEMENT values (1, 'Budapest', 1);
insert into ELEMENT values (2, 'London', 1);
insert into ELEMENT values (3, 'Paris', 1);
insert into BASE_LIST values (2, 'cars');
insert into ELEMENT values (4, 'Opel', 2);
insert into ELEMENT values (5, 'Bmw', 2);
insert into ELEMENT values (6, 'Audi', 2);

insert into CUSTOM_LIST values (1, 'EuCities', 1);
insert into CUSTOM_LIST values (2, 'PriceyCars', 2);

-- the below two inserts are allowed, custom list 1 will have 
-- only two elements from base list 1: 1 and 3
insert into CUSTOM_LISTS_ELEMENTS values (1, 1);
insert into CUSTOM_LISTS_ELEMENTS values (1, 3);

-- this should be forbidden, because element 4 is in base list 2,
-- but custom list 1 is only for elements from base list 1.
insert into CUSTOM_LISTS_ELEMENTS values (1, 4);

sql foreign-keys relational-database relationship

来源：https://stackoverflow.com/questions/63135906/how-to-disallow-custom-lists-to-have-elements-from-different-base-lists

2条答案

按热度按时间

oo7oh9g91#

为了防止来自不同列表的自定义元素，您需要使用复合键。它们将沿着两个关系分支向下移动，并强制每个自定义元素属于一个基列表。
例如，您可以执行以下操作：

create table base_list (
  id int primary key not null,
  name varchar(50)
);

create table element (
  id int not null,
  name varchar(50),
  base_list_id int references base_list (id),
  primary key (base_list_id, id)
);

create table custom_list (
  id int not null,
  name varchar(50),
  base_list_id int references base_list (id),
  primary key (base_list_id, id)
);

create table_custom_list_element (
  custom_list_id int not null,
  base_list_id int not null,
  element_id int not null,
  constraint fk_clist foreign key (base_list_id, custom_list_id) 
    references custom_list (base_list_id, id),
  constraint fk_celement foreign key (base_list_id, element_id) 
    references element (base_list_id, id)
);

特别注意，上一个表中的两个外键共享同一列 base_list_id . 这就是你想要的规则。

赞(0）回复(0）举报 2021-07-29

wdebmtf22#

解决方案的功劳应该归于“刺客”。
请注意，这是使用复合外键的解决方案：

修改后的ddl：

CREATE TABLE BASE_LIST
(
  ID  NUMBER (18) NOT NULL ,
  NAME VARCHAR2 (50) NOT NULL
);
ALTER TABLE BASE_LIST ADD CONSTRAINT BASE_LIST_PK PRIMARY KEY ( ID ) ;

CREATE TABLE ELEMENT
(
  ID  NUMBER (18) NOT NULL ,
  NAME VARCHAR2 (50) NOT NULL,
  BASE_LIST_ID NUMBER (18) NOT NULL
);
ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_PK PRIMARY KEY ( ID ) ;
ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_UK_ID_BASE_LIST_ID UNIQUE (ID, BASE_LIST_ID) ;
ALTER TABLE ELEMENT ADD CONSTRAINT ELEMENT_FK_TO_BASE_LIST FOREIGN KEY ( BASE_LIST_ID ) REFERENCES BASE_LIST ( ID );

CREATE TABLE CUSTOM_LIST
(
  ID  NUMBER (18) NOT NULL ,
  NAME VARCHAR2 (50) NOT NULL ,
  BASE_LIST_ID NUMBER (18) NOT NULL
);
ALTER TABLE CUSTOM_LIST ADD CONSTRAINT CUSTOM_LIST_PK PRIMARY KEY ( ID ) ;
ALTER TABLE CUSTOM_LIST ADD CONSTRAINT CUSTOM_LIST_UK_ID_BASE_LIST_ID UNIQUE (ID, BASE_LIST_ID) ;
ALTER TABLE CUSTOM_LIST ADD CONSTRAINT CUSTOM_LIST_FK_TO_BASE_LIST FOREIGN KEY ( BASE_LIST_ID ) REFERENCES BASE_LIST ( ID );

CREATE TABLE CUSTOM_LISTS_ELEMENTS
(
  BASE_LIST_ID NUMBER (18) NOT NULL,
  CUSTOM_LIST_ID NUMBER (18) NOT NULL,
  ELEMENT_ID NUMBER (18) NOT NULL
);
ALTER TABLE CUSTOM_LISTS_ELEMENTS ADD CONSTRAINT CUSTOM_LISTS_ELEMENTS_PK PRIMARY KEY ( CUSTOM_LIST_ID, ELEMENT_ID ) ;
ALTER TABLE CUSTOM_LISTS_ELEMENTS ADD CONSTRAINT FK_TO_CUSTOM_LIST FOREIGN KEY ( CUSTOM_LIST_ID, BASE_LIST_ID ) REFERENCES CUSTOM_LIST ( ID, BASE_LIST_ID );
ALTER TABLE CUSTOM_LISTS_ELEMENTS ADD CONSTRAINT FK_TO_ELEMENT FOREIGN KEY ( ELEMENT_ID, BASE_LIST_ID ) REFERENCES ELEMENT ( ID, BASE_LIST_ID );

改良试验：

insert into BASE_LIST values (1, 'cities');
insert into ELEMENT values (1, 'Budapest', 1);
insert into ELEMENT values (2, 'London', 1);
insert into ELEMENT values (3, 'Paris', 1);
insert into BASE_LIST values (2, 'cars');
insert into ELEMENT values (4, 'Opel', 2);
insert into ELEMENT values (5, 'Bmw', 2);
insert into ELEMENT values (6, 'Audi', 2);

insert into CUSTOM_LIST values (1, 'EuCities', 1);
insert into CUSTOM_LIST values (2, 'PriceyCars', 2);

-- the below two inserts are allowed, custom list 1 will have 
-- only two elements from base list 1: 1 and 3
insert into CUSTOM_LISTS_ELEMENTS values (1, 1, 1);
insert into CUSTOM_LISTS_ELEMENTS values (1, 1, 3);

-- this should be forbidden, because element 4 is in base list 2,
-- but custom list 1 is only for elements from base list 1.
insert into CUSTOM_LISTS_ELEMENTS values (2, 1, 4);

sql小提琴：
http://sqlfiddle.com/#!4/3306c6/2号

赞(0）回复(0）举报 2021-07-29

我来回答

如何禁止自定义列表包含来自不同基列表的元素

2条答案

相关问题

热门标签

最新问答