Re: How to use a cross column exclude constraint

The convention on these lists is to inline/trim or bottom/trim post your
replies like I did below and previously:

On Thu, Aug 25, 2022 at 4:47 AM <awolchute(at)tutanota(dot)com> wrote:

>
> What does this part of your annotation mean: "slot {check slot in (1,2);
> not null}, {PK: (edge_id, slot)}, {Unique: node_id}]"? The whole Node-Edge
> part is a bit fuzzy for me.
>
>
Slot is there to ensure that no more than 2 nodes can be attached to the
same edge. Since each slot requires a number, the numbers must be unique,
and only the numbers 1 and 2 are available, that is accomplished.

> I have written the following schema:
>
> -- node
> CREATE TABLE api_endpoints (
> id UUID PRIMARY KEY,
> api_endoint_edge_id UUID REFERENCES api_endpoint_edges (id),
> UNIQUE (id, api_endpoint_edge_id), -- did I interpret this correctly?
> ...
> );
>

Yes, while redundant since id is already unique it is required for the
foreign key to work.

>
> -- edge
> CREATE TABLE api_endpoint_edges (
> id UUID PRIMARY KEY,
> ... -- ton of data
> );
>
> -- node-edge, how should i name this table? is just dropping the
> pluralization readable?
> CREATE TABLE api_endpoint_edge (
> id UUID PRIMARY KEY,
> api_endoint_id UUID REFERENCES api_endpoints (id),
> api_endoint_edge_id UUID PRIMARY KEY REFERENCES api_endpoints (id),
> -- what is slot?
> );
>
>
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.