Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRNDN-0007oy-JZ for pgsql-novice@arkaria.postgresql.org; Fri, 26 Aug 2022 00:28:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oRNDL-0004JS-Gk for pgsql-novice@arkaria.postgresql.org; Fri, 26 Aug 2022 00:28:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRNDL-0004Gv-5H for pgsql-novice@lists.postgresql.org; Fri, 26 Aug 2022 00:28:47 +0000 Received: from mail-ej1-x630.google.com ([2a00:1450:4864:20::630]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oRNDG-0004wL-Ra for pgsql-novice@lists.postgresql.org; Fri, 26 Aug 2022 00:28:46 +0000 Received: by mail-ej1-x630.google.com with SMTP id og21so312291ejc.2 for ; Thu, 25 Aug 2022 17:28:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc; bh=MrNJ6774XloS9xycmAirv/r475J0h43kz2dQF745+lE=; b=iO4g13XwcONiKcrAngCdtqP4QB0iEAmA5SIcTEiRHFYtYqW7eb4PwPR3GR27xpr/hw 4R7BtUkX02R7NHE8tR+bGlKXd4xVucxKkdclmDvi9MNbS7uazvl+Dy4UcJyqFrr8TkVH NgMPUEr/iRL9Jr4azaQRi6TDXvzjB0awk4emUbj3ANdFTo7xtdeonea6POwdBtJokgjt bU3nGFRFHIAUQUwIPisqhsLuEdpnLyub7iRkL6SBTVk+0NVc2L7xEeiBR3UzpUDK0EL/ YRk1tt9PZ44g7h0ndwT2I+OqJkRwUkJG5OjDsjiycEgQ7kticat/phepdDL+MmCl7vC5 Km1A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc; bh=MrNJ6774XloS9xycmAirv/r475J0h43kz2dQF745+lE=; b=BaainHf3qJLNRaZyPVN76l3G8LANGJsbJVg0T+PrdlIV7RAHJSSF2MC23i0jlTBwUP odyX9U7BFHZK0dPaxS9dp+jwwhm9WNk6y2R7JY16yMl1Cy2tqsxm+GRI0SyLJdFF0FRc CGNC8N4xuPYlUObfnrCQzjpv7SsFsPBj6dLDtWY/PEu0H+xMNQMMKAwwMgtD7EQbBbzo 9xGo0RPhe+eRALL3gKO/S92mpBbK86arHPIPUR/9zaXC+jqukhK+g1fqY1BCVguk5WhY lu9eNFdmH4/cOzal8eN492ZMC1T4i0qI1hs5XwG04g5wsYozW5HFZJbqSs2YxKuNXo6I Y5JQ== X-Gm-Message-State: ACgBeo2E+PiwvLFsyHOx44jnr+nC+r8LyAbnuWat1g76WYGK6aPJLv9y HA4kz8TfvRn9psxGZTHMnq+DccPF9AH8TDIKhVTEYnD1E1Y= X-Google-Smtp-Source: AA6agR5eF4MfJmIekwt4Ift0SBqwuJfqBGZ9zM56OnJXKRZuUNs4M3wbFr38PWU3Tl/y33GA3sZPvfkwGtzzWyEGsW8= X-Received: by 2002:a17:907:2bec:b0:73d:7353:d1cd with SMTP id gv44-20020a1709072bec00b0073d7353d1cdmr3761099ejc.424.1661473720901; Thu, 25 Aug 2022 17:28:40 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Thu, 25 Aug 2022 17:28:22 -0700 Message-ID: Subject: Re: How to use a cross column exclude constraint To: awolchute@tutanota.com Cc: Pgsql Novice Content-Type: multipart/alternative; boundary="000000000000f022e605e719fe03" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f022e605e719fe03 Content-Type: text/plain; charset="UTF-8" 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 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. --000000000000f022e605e719fe03 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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@tutanota.co= m> wrote:
=20 =20 =20

What does this part of your a= nnotation 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 f= or me.


Slot is there to ensure that no more than 2 nodes can be attache= d to the same edge.=C2=A0 Since each slot requires a number, the numbers mu= st be unique, and only the numbers 1 and 2 are available, that is accomplis= hed.

=C2=A0
I have written t= he following schema:

-- node
CREATE TABLE api_endpoints (
<= div dir=3D"auto">=C2=A0 id UUID PRIMARY KEY,
=C2= =A0 api_endoint_edge_id UUID REFERENCES api_endpoint_edges (id),
<= div dir=3D"auto">=C2=A0 UNIQUE (id, api_endpoint_edge_id), -- did I interpr= et this correctly?
=C2=A0 ...=C2=A0 =C2=A0
);

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

-- edge
CREATE TABLE api_endpoint_edges (
<= /div>
=C2=A0 id UUID PRIMARY KEY,
=C2=A0 ...=C2=A0 -- ton of data=C2=A0
);
<= /div>

-- node-edge, how should= i name this table? is just dropping the pluralization readable?
<= div dir=3D"auto">CREATE TABLE api_endpoint_edge (
=C2=A0 id UUID PRIMARY KEY,
=C2=A0 api_endoint= _id UUID REFERENCES=C2=A0api_endpoints (id),
=C2= =A0 api_endoint_edge_id UUID PRIMARY KEY REFERENCES=C2=A0api_endpoints (id)= ,
=C2=A0 -- what is slot?
);


IMO table names should not be plural - tables are also types an= d types are named singular (e.g., integer).=C2=A0 Joining tables usually ju= st combine the names of the tables they join.

The joi= ning table isn't technically required, you could put slot and those con= straints 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 uniq= ue anyway.=C2=A0 The combination of edge_id and node_id is unique.=C2=A0 Th= e 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 (
=C2=A0 api_endpoint_id = uuid primary key,
=C2=A0 api_endpoint_edge_id uuid references (api_= endpoint_edge.api_endpoint_edge_id),
=C2=A0 unique (api_endpoint_ed= ge_id, api_endpoint_id) -- this goes away if slot moves here; with a modifi= ed check constraint probably...
=C2=A0 -- edge first to make the in= dex more generally useful for searching for edges, and api_endpoint_id is a= lready first in the PK index
);

CREATE TABLE a= pi_endpoint_edge (
=C2=A0 api_endpoint_edge_id uuid primary key
=
);

CREATE TABLE api_endpoint_edge (
= =C2=A0 api_endpoint_id uuid not null primary key,
=C2=A0 api_endpoi= nt_edge_id uuid not null,

=C2=A0 foreign key (api_endp= oint_id, api_endpoint_edge_id)
=C2=A0 =C2=A0 references api_en= dpoint (api_endpoint_id, api_endpoint_edge_id),

=C2=A0= -- you can place these on api_endpoint and get rid of this table
= =C2=A0 slot integer check (slot IN (1,2)) not null,
=C2=A0 uniq= ue(api_endpoint_edge_id, slot)
);

David = J.

--000000000000f022e605e719fe03--