how to disallow custom lists to have elements from different base lists

I have the following model:

1. BASE_LIST: in this table we store base lists. Base list can be for example the list of cities in a continent, types of automobiles in a shop etc.

2. ELEMENT: in this table we store the elements of base lists. For example the cities and the car types are here (Budapest, London, Paris, Opel, Bmw, Audi)

3. CUSTOM_LIST: in this table we store customizations of the lists. The customization means filtering. For example there can be a custom list called ‘european cities’ which is a subset of the cities. Or pricey cars which is a subset of the cars. A custom list must have exactly one parent – base list, and only elements from that base list can be in it.

The current representation is this:

The relation between BASE_LIST and ELEMENT is a one to many relation (an element can be a part of only one base list, but a base list can have many elements).

The relation between BASE_LIST and CUSTOM_LIST is a one to many relation, each custom list must have exactly one “parent list”.

The relation between the CUSTOM_LIST and ELEMENT is a many to many relationship, because

• an element can be part of more than one custom lists: for example a car may be in a custom list called “pricey cars” and in an other custom list called “unreliable cars” too.
• and a custom list of course can have multiple elements, therefore the many to many relation.

The problem is that this structure allows a custom list with elements from different base lists.

We would like to prohibit this. In other words a custom list with elements from the “cars” base list is OK, a custom list with elements from the “cities” list is OK, but a custom list with cars and cities mixed is NOT OK.

Is there a way to prohibit such mixed lists with standard constraints (no stored procedures etc)?

I have created a fiddle for this:

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

Problem:

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

Answer

In order to prevent custom elements from different lists, you’ll need to use composite keys. These will travel down both the relationships brachches and will enforce each custom element to belong to a single base list.

For example you can do:

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

In particular, notice both foreign keys in the last table share the same column base_list_id. That enforces the rule you want.