Yes, while redundant since id is already unique it is required for the foreign key to work.
IMO table names should not be plural - tables are also types and types are named singular (e.g., integer). Joining tables usually just combine the names of the tables they join.
The joining table isn't technically required, you could put slot and those constraints on the node table as well.
You don't get to have two primary keys on a table, and api_edpoint_edge_id isn't unique anyway. The combination of edge_id and node_id is unique. The id field (and I abhor using "id" for a column name) doesn't really do much here, there are not going to be external joins to it.
CREATE TABLE api_endpoint (
api_endpoint_id uuid primary key,
api_endpoint_edge_id uuid references (api_endpoint_edge.api_endpoint_edge_id),
unique (api_endpoint_edge_id, api_endpoint_id) -- this goes away if slot moves here; with a modified check constraint probably...
-- edge first to make the index more generally useful for searching for edges, and api_endpoint_id is already first in the PK index
);
CREATE TABLE api_endpoint_edge (
api_endpoint_edge_id uuid primary key
);
CREATE TABLE api_endpoint_edge (
api_endpoint_id uuid not null primary key,
api_endpoint_edge_id uuid not null,
foreign key (api_endpoint_id, api_endpoint_edge_id)
references api_endpoint (api_endpoint_id, api_endpoint_edge_id),
-- you can place these on api_endpoint and get rid of this table
slot integer check (slot IN (1,2)) not null,
unique(api_endpoint_edge_id, slot)
);
David J.
