Many to many unique combination

Question

So I originally thought I was going to have a one to many relationship in a database with the following tables

ENTITY_JSON_MAP
ATTRIBUTE_JSON_MAP

Originally I was going to have an FK in ATTRIBUTE_JSON_MAP pointing to ENTITY_JSON_MAP. I was going to the Attribute table with

alter table ATTRIBUTE_JSON_MAP add unique (NAME, JSON_MAP_ENUM, ENTITY_JSON_MAP_FK)

Now however I've realized that I need to make it a many to many relationship, so I've created a mapping table, but now I've lost the ability to make sure that I don't get duplicates of that combination.

I was hoping there was still a way to do this. Inserting/deleting tables doesn't need to be fast, as the table will not be inserted or deleted at run time.

Answer 1

If I understand you correctly, your original

Unique (name, json_map_enum, entity_json_map_fk)

Is made up of

name: name of the attribute json_map_enum: the position of this attribute in the entity entity_json_map_fk: reference to entity

You should be able to enforce this in the mapping table as well.

CREATE TABLE Entity_Attribute_Map (
    ATTRIBUTE_JSON_MAP_fk int references ATTRIBUTE_JSON_MAP(id),
    entity_json_map_fk int references entity_json_map(id),
    JSON_MAP_ENUM int,
    Primary Key (ATTRIBUTE_JSON_MAP_fk, entity_json_map_fk, JSON_MAP_ENUM)
)