Precedence: bulk ------=_Part_877440_1561249341.1661111117651 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi All, Schema: CREATE TABLE record ( id uuid=C2=A0primary key default ..., ...);= CREATE TABLE record_pointer=C2=A0( id uuid=C2=A0primary key default ...,= record_a_id uuid=C2=A0not null references record (id), record_b_id u= uid=C2=A0not null references record (id), ...); I am trying to create an exclude constraint to make both record_a_id and re= cord_b_id unique table wide, so that each row from "record" can ever be ref= erenced once in "record_pointers".=C2=A0 Eg. if I add a record_pointer row that has "this-is-random-uuid" as record_= a_id, the value "this-is-random-uuid" can never be in record_a_id or record= _b_id in any other row. Thank you in advance! ------=_Part_877440_1561249341.1661111117651 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

Hi All,

Schema:

CREATE TABLE record (    
    id uuid primary key default ...,
    ...
);

CREATE TABLE record_pointer (
    id uuid primary key default ...,
    record_a_id uuid not null references record (id),
    record_b_id uuid not null references record (id),
    ...
);

I am trying to create an exclude constraint to make both record_a_id and record_b_id unique table wide, so that each row from "record" can ever be referenced once in "record_pointers".

Eg. if I add a record_pointer row that has "this-is-random-uuid" as record_a_id, the value "this-is-random-uuid" can never be in record_a_id or record_b_id in any other row.

Thank you in advance!

------=_Part_877440_1561249341.1661111117651-- From mchidamparam@gmail.com Sat Jun 6 11:55:30 2026 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 1oQVCH-00054L-Nq for pgsql-novice@arkaria.postgresql.org; Tue, 23 Aug 2022 14:48:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQVCG-0008B0-MP for pgsql-novice@arkaria.postgresql.org; Tue, 23 Aug 2022 14:48:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQVCG-0008Ap-CL for pgsql-novice@lists.postgresql.org; Tue, 23 Aug 2022 14:48:04 +0000 Received: from mail-lf1-x12c.google.com ([2a00:1450:4864:20::12c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oQVCD-0006Zw-Rv for pgsql-novice@lists.postgresql.org; Tue, 23 Aug 2022 14:48:03 +0000 Received: by mail-lf1-x12c.google.com with SMTP id s1so17205746lfp.6 for ; Tue, 23 Aug 2022 07:48:01 -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=vothdmIkdVAuCXP3KRhrqF5vM4iF+0zPVDT/v7Ct1eE=; b=p0UTG2hNNTVV1oTyxAfH4VRtgCJdnK7BDoq6aSQyof9fDCYxFABuDshQPRjWcC8HTf X1Q8/W4kyWrl1GktBpHcSW+G7fVjX/kmwr3lI+7/F461WcoSq04iI+NmJOwkYdtr+wU8 OZE10n8slrMRc2gbnlCKmTJXsgAVxRi4YFjTlDRhtvv8da6y5IYfUxdm+37mD8pXmLJI efnYNTTufIyUpVBd69p/ynQFj4pXOm4t4LNzPvp3gJg7cN2RqH8zDaVBu+HqdilcOsx9 NTdonpUK5x3ArdtdYtZLQ8BN/xZE5Ko6tWzi8DijAk2uH2QaggW0cMKeXf+7eVxq2map gq2A== 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=vothdmIkdVAuCXP3KRhrqF5vM4iF+0zPVDT/v7Ct1eE=; b=zmKg4w4rDdClNCng3Wo4kOa9VBesV8cPlA1iG1IvqnreWfMUK9e0HeSUXKnPrarGKO l5QApsuZREsUTifN1SRTV/8i6dR9dR7I4IHlkJASpUJZMsLFjdZU8AGoZpvgDlesVYpc XqlsAIj+YAK4zQwokLD/I1kmQ7Bd8J+vj6orm6cRNh3ScN/SsfGoKiKn9xuGTgVWQJA7 dgzjksHl7QnCRu/FoJ9rRvBOYVG1APXQKKZcsAL0Osc/ziJ1DN/8az4edQ4SoICvpnir TeOnutq9q9sfcohvHStKCKiXoH2R6EEAeFWfva3YGGjW9GEYGbrbZ6ws2YGiMDt9p3if IzHg== X-Gm-Message-State: ACgBeo2ESPdjXiVkzeYba7nDOI+oKR8/i5a5UDA3LVLenOYFd/tH9J35 kOGJOz7LwpnR6Kr15YJQ85v4aUx/g/AJEZPlzfY= X-Google-Smtp-Source: AA6agR5qFDmMdP3UaujJOU2SHImLp3J++NJqf7USETelN0Rbu50YroiBnJBiNyACLPfxOGDGSJGuF1IWY7ri0ZMX8fU= X-Received: by 2002:a19:e00d:0:b0:492:e5a5:588b with SMTP id x13-20020a19e00d000000b00492e5a5588bmr2926737lfg.243.1661266080148; Tue, 23 Aug 2022 07:48:00 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: chidamparam muthusamy Date: Tue, 23 Aug 2022 20:17:46 +0530 Message-ID: Subject: Re: How to use a cross column exclude constraint To: awolchute@tutanota.com Cc: Pgsql Novice Content-Type: multipart/alternative; boundary="00000000000095b36005e6e9a6aa" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000095b36005e6e9a6aa Content-Type: text/plain; charset="UTF-8" Refer https://www.postgresql.org/docs/current/ddl-constraints.html CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) ); In the above example, column 'discounted_price' value is checked that it should be less than the column value, 'price'. Is it possible to add similar check condition for the columns, 'record_a_id' and 'record_b_id' that is CHECK(record_a_id != record_b_id) Regards, Chidamparam On Mon, Aug 22, 2022 at 1:15 AM wrote: > > Hi All, > > Schema: > > CREATE TABLE record ( > id uuid primary key default ..., > ... > ); > > CREATE TABLE record_pointer ( > id uuid primary key default ..., > record_a_id uuid not null references record (id), > record_b_id uuid not null references record (id), > ... > ); > > > I am trying to create an exclude constraint to make both record_a_id and > record_b_id unique table wide, so that each row from "record" can ever be > referenced once in "record_pointers". > > Eg. if I add a record_pointer row that has "this-is-random-uuid" as > record_a_id, the value "this-is-random-uuid" can never be in record_a_id or > record_b_id in any other row. > > > Thank you in advance! > > > --00000000000095b36005e6e9a6aa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Refer=C2=A0https://www.postgresql.org/docs/current/ddl-constraint= s.html

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price=
 > discounted_price)
);

In the above example, column 'discounted_price'=C2=A0 valu= e is checked that it should be less than the column value, 'price'.=

Is it possible to add similar check condition for the columns, 'record= _a_id' and 'record_b_id' that is

CHECK(record_a_id !=3D record_= b_id)

Regards,

Chidamparam

<= /div>

On Mon, Aug 22, 2022 at 1:15 AM <awolchute@tutanota.com> wrote:

=20 =20 =20

Hi All,

Schema:

CREATE T=
ABLE record (   =20
    id uuid=C2=A0primary key default ...,
    ...
);

CREATE TABLE record_pointer=C2=A0(
    id uuid=C2=A0primary key default ...,
    record_a_id uuid=C2=A0not null references record (id),
    record_b_id uuid=C2=A0not null references record (id),
    ...
);

I am trying to create a= n exclude constraint to make both record_a_id and record_b_id unique table = wide, so that each row from "record" can ever be referenced once = in "record_pointers".=C2=A0

Eg. if I add a record_pointer row that= has "this-is-random-uuid" as record_a_id, the value "this-i= s-random-uuid" can never be in record_a_id or record_b_id in any other= row.

Thank you in advance!

--00000000000095b36005e6e9a6aa-- From david.g.johnston@gmail.com Sat Jun 6 11:55:30 2026 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 1oQWFX-0000UR-Pb for pgsql-novice@arkaria.postgresql.org; Tue, 23 Aug 2022 15:55:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQWFW-0006Zx-MM for pgsql-novice@arkaria.postgresql.org; Tue, 23 Aug 2022 15:55:30 +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 1oQWFW-0006Zl-BA for pgsql-novice@lists.postgresql.org; Tue, 23 Aug 2022 15:55:30 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oQWFT-0001wT-LC for pgsql-novice@lists.postgresql.org; Tue, 23 Aug 2022 15:55:29 +0000 Received: by mail-ej1-x629.google.com with SMTP id n7so10159344ejh.2 for ; Tue, 23 Aug 2022 08:55:27 -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=bw+G1KuD+ir+b+evqptFKkV6lI4RO2YeLlxFrRzmgYo=; b=jia6RLcRT1xzPPwyjKkgP8KbaZbq+ZPW6jlzCgmTpMOYBmOTxiA/gd8W1ubMJ7TZU/ cakxN/mlaHiY2CEoQB96y6mj8kJGequmzi9Xaii4jgTa3NW6NIYdyL2yH3f0dlUfYAN4 CfSCqxkhCTj5N7AAdR9jRoc4e6C9gBxDYNEKddye/DiV24rYQQuHuiqOioap2zb2WDfX rgB4oLkOg8hMhrMd9aUlcfbFFLjkkARkNK9obSDqrZoDQVqKiQq8ueQq28SlEKewl+WR 4+szlPndxhskHh85sPbWYTRjFQv/kQfabYI5nj+aT7ixa1Q5r6NEhLFRX4JxRnIG+MLH gxpw== 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=bw+G1KuD+ir+b+evqptFKkV6lI4RO2YeLlxFrRzmgYo=; b=fP/iuQwU6FLs1+HbO1UHRptM27q/Iw2VA3Alt4rc++TGjtay7M9kmGK7LgbBlYlXi4 caby2E+hlOO02bHiMrc3ScigeIUg/8Hp3hEsbSGV7hxCUe8QpJY7jTVsjjtmJA9QhA7x rIgaV+qkOIBxfmz2+JQCSd7l3ZG7QodJTsF/Hxncqy2yyTb2tvH3F3owJFRI7XZaPxob 2d0QSviYX5gCN2UbGeYk72YKZr35AjZGRaVHYXW2w+QYSAw9IjiK8OOlUC7YiQVYV0yN ZnUmUam+NTgN3DOnOhHee++Q7KOOX7C+rcJKsz9pnC8ZMaQMYM6QeQo8vZd9bOtJMddd xVKw== X-Gm-Message-State: ACgBeo00rN/FOnJf6oor4k9MI56c3aOCy1fSdLp1HHnsB9fG8AXb2JfQ +uskZ8NvqKvudKt+CKTdCDug89s/XeT86oEon/Q= X-Google-Smtp-Source: AA6agR4/gCTsUhReVikXqBHbY5cWAp6dd6J6y8tQFeawJxM0lGyBb2qsLlPSFbJermidsCJomVG/KTTCG9y60XhNSYc= X-Received: by 2002:a17:906:cc4c:b0:73d:a494:5201 with SMTP id mm12-20020a170906cc4c00b0073da4945201mr147967ejb.259.1661270126397; Tue, 23 Aug 2022 08:55:26 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 23 Aug 2022 08:55:09 -0700 Message-ID: Subject: Re: How to use a cross column exclude constraint To: chidamparam muthusamy Cc: awolchute@tutanota.com, Pgsql Novice Content-Type: multipart/alternative; boundary="000000000000c28c6305e6ea97c9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c28c6305e6ea97c9 Content-Type: text/plain; charset="UTF-8" On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy < mchidamparam@gmail.com> wrote: > Refer https://www.postgresql.org/docs/current/ddl-constraints.html > > CREATE TABLE products ( > product_no integer, > name text, > price numeric CHECK (price > 0), > discounted_price numeric CHECK (discounted_price > 0), > CHECK (price > discounted_price) > ); > > In the above example, column 'discounted_price' value is checked that it > should be less than the column value, 'price'. > Is it possible to add similar check condition for the columns, > 'record_a_id' and 'record_b_id' that is > CHECK(record_a_id != record_b_id) > >> >> The declarative constraints available do not allow for a "diagonal" definition. CHECK constraints are row-limited but can reference any columns. Exclusion constraints are column-oriented, the specified columns are compared to the same columns in all other rows. You cannot declare that a value in column b exists or does not exist in column a on a different row. You can write a trigger to that effect if you'd like. The absence of a declarative feature for this is because this model is non-normalized and the features of SQL are generally designed to help implement normalized data models. You should consider whether you can redesign things so that you can leverage the features present in the language; and the efficiencies and robustness that such features tend to have that custom trigger code may lack. David J. --000000000000c28c6305e6ea97c9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy <= mchidamparam@gmail.com> wr= ote:

Refer=C2=A0https://www.postgresql.org/docs= /current/ddl-constraints.html
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_pr=
ice)
);
In the above ex= ample, column 'discounted_price'=C2=A0 value is checked that it sho= uld be less than the column value, 'price'.
Is it possible to add similar check condition f= or the columns, 'record_a_id' and 'record_b_id' that is
CHECK(record_a_id !=3D reco= rd_b_id)

<= /div>

The declarative constraints available d= o not allow for a "diagonal" definition.=C2=A0 CHECK constraints = are row-limited but can reference any columns.=C2=A0 Exclusion constraints = are column-oriented, the specified columns are compared to the same columns= in all other rows.

You cannot declare that a value in= column b exists or does not exist in column a on a different row.=C2=A0 Yo= u can write a trigger to that effect if you'd like.

<= div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif= ">The absence of a declarative feature for this is because this model is no= n-normalized and the features of SQL are generally designed to help impleme= nt normalized data models.=C2=A0 You should consider whether you can redesi= gn things so that you can leverage the features present in the language; an= d the efficiencies and robustness that such features tend to have that cust= om trigger code may lack.

David J.

<= /div>

--000000000000c28c6305e6ea97c9-- From awolchute@tutanota.com Sat Jun 6 11:55:30 2026 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 1oQm0t-0002Gg-Mp for pgsql-novice@arkaria.postgresql.org; Wed, 24 Aug 2022 08:45:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQm0s-0004k4-F4 for pgsql-novice@arkaria.postgresql.org; Wed, 24 Aug 2022 08:45:26 +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 1oQm0s-0004ju-3c for pgsql-novice@lists.postgresql.org; Wed, 24 Aug 2022 08:45:26 +0000 Received: from w1.tutanota.de ([81.3.6.162]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQm0l-0001to-4k for pgsql-novice@lists.postgresql.org; Wed, 24 Aug 2022 08:45:25 +0000 Received: from w3.tutanota.de (unknown [192.168.1.164]) by w1.tutanota.de (Postfix) with ESMTP id F2B95FBF923; Wed, 24 Aug 2022 08:45:17 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1661330717; s=s1; d=tutanota.com; h=From:From:To:To:Subject:Subject:Content-Description:Content-ID:Content-Type:Content-Type:Content-Transfer-Encoding:Cc:Cc:Date:Date:In-Reply-To:In-Reply-To:MIME-Version:MIME-Version:Message-ID:Message-ID:Reply-To:References:References:Sender; bh=wN2Okxg3buuYjEWgKL7XAuxWHDlb+Rz5ejURn9Wa9YQ=; b=ZZpDNdwFNh0k8j+Nk44DREWFOrtRHPw7rjIoNQSi7ZxS9Hpuhy28QkD8weKQ09Cs dwNYLj1NtNHFwTdJfW7LMpREsnYeDfCjzkAbrzy9jdNGs2LyOLDVk1EqtcukF92KyeQ UVZRGvHmuJoTfk42SMCXsQ00zs6s2dy6bkGZe78VEuc8QA1cBHnAgUx9yHI064rCu1m 8o1sb1JEg96/xaPKQtEm2EXkkyWHaG8JYgfz8pY3DEgf/ZQ+AY71Q5fCvj6p3JHJ2mw utzzZBpNhuj7vbr2Zm/jwVqDS4SLrDFKlqx/Lid+43wXVxH1NyrfJDQcNjanv9IWBNC HKj05eAZ5A== Date: Wed, 24 Aug 2022 10:45:17 +0200 (CEST) From: awolchute@tutanota.com To: "David G. Johnston" Cc: Pgsql Novice , Mchidamparam Message-ID: In-Reply-To: References: Subject: Re: How to use a cross column exclude constraint MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_43555_490526727.1661330717976" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_43555_490526727.1661330717976 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, Thank you for the insight! How would you go about modeling my problem correctly? The domain constraints are: - there are many "records" - there are 1:1 links between "records", and the links (table) contain a lo= t of information about the link (so adding a record_id (fk) to the records = table would also add a ton of columns). - the links are bidirectional - each "record" can be linked with exactly one "record", so a record linkin= g to another does not allow the record being referenced to be in any other = link either. - a graph of records and their connections (links) must be efficiently quer= ied / formed Best regards, A Aug 23, 2022, 15:55 by david.g.johnston@gmail.com: > On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy <> mchidamparam@gma= il.com> > wrote: > >> Refer=C2=A0>> https://www.postgresql.org/docs/current/ddl-constraints.ht= ml >> >> CREATE TABLE products ( product_no integer, name text, price nu= meric CHECK (price > 0), discounted_price numeric CHECK (discounted_pric= e > 0), >> CHECK (price > discounted_price)>> ); >> In the above example, column 'discounted_price'=C2=A0 value is checked t= hat it should be less than the column value, 'price'. >> Is it possible to add similar check condition for the columns, 'record_a= _id' and 'record_b_id' that is >> CHECK(record_a_id !=3D record_b_id) >> >>> >>> > > The declarative constraints available do not allow for a "diagonal" defin= ition.=C2=A0 CHECK constraints are row-limited but can reference any column= s.=C2=A0 Exclusion constraints are column-oriented, the specified columns a= re compared to the same columns in all other rows. > > You cannot declare that a value in column b exists or does not exist in c= olumn a on a different row.=C2=A0 You can write a trigger to that effect if= you'd like. > > The absence of a declarative feature for this is because this model is no= n-normalized and the features of SQL are generally designed to help impleme= nt normalized data models.=C2=A0 You should consider whether you can redesi= gn things so that you can leverage the features present in the language; an= d the efficiencies and robustness that such features tend to have that cust= om trigger code may lack. > > David J. > > ------=_Part_43555_490526727.1661330717976 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi,

Thank you f= or the insight!

How = would you go about modeling my problem correctly?

The domain constraints are:

- there are many "records"

- there are= 1:1 links between "records", and the links (table) contain a lot of inform= ation about the link (so adding a record_id (fk) to the records table would= also add a ton of columns).

- the links are bid= irectional

- each "record" can be linked with ex= actly one "record", so a record linking to another does not allow the recor= d being referenced to be in any other link either.

- a graph of records and their connections (links) must be efficiently q= ueried / formed

Best= regards,

<= div>Aug 23, 2022, 15:55 by david.g.johnston@gmail.com:

On Tue= , Aug 23, 2022 at 7:48 AM chidamparam muthusamy <mchidampar= am@gmail.com> wrote:

Refer https://www.postgresql.org/docs/current/ddl-constraints= .html

CREATE TABLE p=
roducts (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)=

);

In the abov= e example, column 'discounted_price' value is checked that it should = be less than the column value, 'price'.

Is it possible to add similar check condition for the c= olumns, 'record_a_id' and 'record_b_id' that is

CHECK(record_a_id !=3D record_b_id)

<= div>

= The declarative constraints available do not allow for a "diagonal" definit= ion. CHECK constraints are row-limited but can reference any columns.= Exclusion constraints are column-oriented, the specified columns are= compared to the same columns in all other rows.

You cannot declare that a v= alue in column b exists or does not exist in column a on a different row.&n= bsp; You can write a trigger to that effect if you'd like.

The absence of a = declarative feature for this is because this model is non-normalized and th= e features of SQL are generally designed to help implement normalized data = models. You should consider whether you can redesign things so that y= ou can leverage the features present in the language; and the efficiencies = and robustness that such features tend to have that custom trigger code may= lack.

David J.

------=_Part_43555_490526727.1661330717976-- From david.g.johnston@gmail.com Sat Jun 6 11:55:30 2026 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 1oQqjX-0008NM-Fb for pgsql-novice@arkaria.postgresql.org; Wed, 24 Aug 2022 13:47:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQqjW-000711-Dn for pgsql-novice@arkaria.postgresql.org; Wed, 24 Aug 2022 13:47:50 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQqjW-00070a-3k for pgsql-novice@lists.postgresql.org; Wed, 24 Aug 2022 13:47:50 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oQqjT-0000hg-OD for pgsql-novice@lists.postgresql.org; Wed, 24 Aug 2022 13:47:49 +0000 Received: by mail-ej1-x62d.google.com with SMTP id kk26so3937700ejc.11 for ; Wed, 24 Aug 2022 06:47:47 -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:references:in-reply-to :mime-version:from:to:cc; bh=mAgOelzbsVl1zUn16IyeC6Spgnjtov6wfLuKnpvFGK8=; b=IaMeV2wtUQgtOFRB30lFMfbEAUKrsxCgWS67kVV3e8fO2e9JOkH7rKtejTCvSOOdph iW613Su3RiK4Hk0cfTAo1/ssOHgC8CF9iiKNYRerGXqnXAfdxArFpzt3VNK1cv4IeGfm Jowe0vhGf706xqrcpKXUmPdyFJiaDFS6t4Yv2ODhjoyxWnH1ONMsV+b2s5gSleLxh0+h CD3BYxitValhq4y8hWL+9n4PNfKnM4EgDCRg2g9LKY9BC1p2RcpasbUyY4cePc8vrOgn vUafc2T6mYRX4ixExNZPWUxYl1xbgyylP286BIJjiolE5JkKsVEJS48SnNZAgW7zA/h+ ZQug== 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:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc; bh=mAgOelzbsVl1zUn16IyeC6Spgnjtov6wfLuKnpvFGK8=; b=ztHa1nJyVPPp1UiPRXlNxauqr+xz2DBZU+HnTX1PJyXvvotE+vOQkaA0CHJbV1uf7c vUmK5YgqcS+HPVg80GzH5LakpgpiWxpAv9gcytEJFbrzEqPfpahiuCx/qodaoXY73oAk u6PEnvbfAsbdkHOPWfhAOzmlhfoSFjcGLuOhfDd2zul3kr1l9oxTukzCg0eDf6PPOCcU c9YaFcow+XZNzYW4ttf+ihDdhOzH1pbVujFeDdudP0Qpeb077Sy3Gz/ovSFZlKM5SMdT Yz6OBRv1WAqHu5pZMRMwYVMUoEAcOE1gu5zUF/hPowIhukpL5DpwHEug4kCNtLjdQjPq NKWA== X-Gm-Message-State: ACgBeo10CQYfIln/Etor4ESArk37Jna5xJXgdqQQwLmbDCue1QYQ5CIS wMPajx61fNLOL1CeiLDtTTcDKzez6bHLrRSmDRk= X-Google-Smtp-Source: AA6agR6AnWhAa0YqRcrsbxWTmnUuhVRT8ABVLDY41lyMsenKobjrL7w7/9tey3luFHe3R+2m+MZJQ2v5HKpZIAQJ4lQ= X-Received: by 2002:a17:907:1689:b0:73d:c059:a18c with SMTP id hc9-20020a170907168900b0073dc059a18cmr1365785ejc.396.1661348865376; Wed, 24 Aug 2022 06:47:45 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:640c:2b09:b0:17f:2bb9:86ff with HTTP; Wed, 24 Aug 2022 06:47:44 -0700 (PDT) In-Reply-To: References:

From: "David G. Johnston" Date: Wed, 24 Aug 2022 06:47:44 -0700 Message-ID: Subject: Re: How to use a cross column exclude constraint To: "awolchute@tutanota.com" Cc: Pgsql Novice , Mchidamparam Content-Type: multipart/alternative; boundary="000000000000f8073f05e6fceca1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f8073f05e6fceca1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wednesday, August 24, 2022, wrote: > Hi, > > Thank you for the insight! > > How would you go about modeling my problem correctly? > > The domain constraints are: > - there are many "records" > - there are 1:1 links between "records", and the links (table) contain a > lot of information about the link (so adding a record_id (fk) to the > records table would also add a ton of columns). > - the links are bidirectional > - each "record" can be linked with exactly one "record", so a record > linking to another does not allow the record being referenced to be in an= y > other link either. > - a graph of records and their connections (links) must be efficiently > queried / formed > Node: [node_id PK, edge_id {FK edge.edge_id}, {Unique: node_id, edge_id)] Edge: [edge_id PK, =E2=80=A6] Node-Edge: [(node_id, edge_id) {FK node.node_id, node.edge_id}, slot {check slot in (1,2); not null}, {PK: (edge_id, slot)}, {Unique: node_id}] That doesn=E2=80=99t enforce =E2=80=9Cnot zero=E2=80=9D or missing records,= which is possible but generally a pain, but does enforce that a node may have at most one edge, and each edge has at most two nodes. With a deferred not null constraint on node.esge_id I think you can solve prevent missing links problem, assuming you always add nodes in pairs. You=E2=80=99d do so ething similar with edge.edge_id if you wanted to avoid dangling edges (edges without nodes). David J. --000000000000f8073f05e6fceca1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wednesday, August 24, 2022, <awolchute@tutanota.com> wrote:

=20 =20 =20

Hi,

Thank you f= or the insight!

How = would you go about modeling my problem correctly?

The domain constraints are:
- there are many "records"
-= there are 1:1 links between "records", and the links (table) con= tain a lot of information about the link (so adding a record_id (fk) to the= records table would also add a ton of columns).
- the links are bidirectional
- each "reco= rd" can be linked with exactly one "record", so a record lin= king to another does not allow the record being referenced to be in any oth= er link either.
- a graph of records and their c= onnections (links) must be efficiently queried / formed

Node: [node_id PK, edge_id {FK edge.edge_id}, {Uni= que: node_id, edge_id)]
Edge: [edge_id PK, =E2=80=A6]
N= ode-Edge: [(node_id, edge_id) {FK node.node_id, node.edge_id}, slot {check = slot in (1,2); not null}, {PK: (edge_id, slot)}, {Unique: node_id}]

That doesn=E2=80=99t enforce =E2=80=9Cnot zero=E2=80=9D o= r missing records, which is possible but generally a pain, but does enforce= that a node may have at most one edge, and each edge has at most two nodes= .

With a deferred not null constraint on node.esge= _id I think you can solve prevent missing links problem, assuming you alway= s add nodes in pairs.=C2=A0 You=E2=80=99d do so ething similar with edge.ed= ge_id if you wanted to avoid dangling edges (edges without nodes).

David J.

--000000000000f8073f05e6fceca1-- From awolchute@tutanota.com Sat Jun 6 11:55:30 2026 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 1oRBKo-0002Ot-IL for pgsql-novice@arkaria.postgresql.org; Thu, 25 Aug 2022 11:47:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oRBKm-0007aP-MK for pgsql-novice@arkaria.postgresql.org; Thu, 25 Aug 2022 11:47:40 +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 1oRBKm-0007Zt-AV for pgsql-novice@lists.postgresql.org; Thu, 25 Aug 2022 11:47:40 +0000 Received: from w1.tutanota.de ([81.3.6.162]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oRBKi-0006vf-T7 for pgsql-novice@lists.postgresql.org; Thu, 25 Aug 2022 11:47:37 +0000 Received: from w3.tutanota.de (unknown [192.168.1.164]) by w1.tutanota.de (Postfix) with ESMTP id 8E95CFBF8D3; Thu, 25 Aug 2022 11:47:35 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1661428055; s=s1; d=tutanota.com; h=From:From:To:To:Subject:Subject:Content-Description:Content-ID:Content-Type:Content-Type:Content-Transfer-Encoding:Cc:Cc:Date:Date:In-Reply-To:In-Reply-To:MIME-Version:MIME-Version:Message-ID:Message-ID:Reply-To:References:References:Sender; bh=KkTnowxknGzNoYc4LQvdXk9BK9air8L6YYm9+409VmU=; b=MlFj9SBBiIvpPD19WwxaYwakx5sACBypP9/beoN3mztW0diIG0Z1ZeUMZyAcGYUc r64y6plAyVhHb6fXWaMKide0uKLYyK5KekmnbuAQ9/tJKEqXHPf/Cdy6jdHUuuqc74k eNldJchhUSd35Spd1VA33+EFdhi7prKvYVOsepDLSVHZvw5DradVeAfVieitgR03f3c l2+AaVaAWMgCz14mK1FYEqUPLvb3JdDtrmLrl1WyLbpVRol5bp1ezbpDDUeKAOig3B2 27UaLVR3a+WDnqnsGpm1IyENGt2OrYLn9DnhrtqLi0taKGWFnqJ46EirOfdWig31yY5 lyTN3uSDXQ== Date: Thu, 25 Aug 2022 13:47:35 +0200 (CEST) From: awolchute@tutanota.com To: "David G. Johnston" Cc: Pgsql Novice Message-ID: In-Reply-To: References:

Subject: Re: How to use a cross column exclude constraint MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4910_997264664.1661428055569" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_4910_997264664.1661428055569 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, What does this part of your annotation mean: "slot {check slot in (1,2); no= t null}, {PK: (edge_id, slot)}, {Unique: node_id}]"? The whole Node-Edge pa= rt is a bit fuzzy for me. I have written the following schema: -- node CREATE TABLE api_endpoints ( =C2=A0 id UUID PRIMARY KEY, =C2=A0 api_endoint_edge_id UUID REFERENCES api_endpoint_edges (id), =C2=A0 UNIQUE (id, api_endpoint_edge_id), -- did I interpret this correctly= ? =C2=A0 ...=C2=A0 =C2=A0 ); -- edge CREATE TABLE api_endpoint_edges ( =C2=A0 id UUID PRIMARY KEY, =C2=A0 ...=C2=A0 -- ton of data=C2=A0 ); -- node-edge, how should i name this table? is just dropping the pluralizat= ion readable? 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? ); Thank you for helping me get started with Postgres! > > Aug 24, 2022, 13:47 by david.g.johnston@gmail.com: > >> On Wednesday, August 24, 2022, <>> awolchute@tutanota.com>> > wrote: >> >>> Hi, >>> >>> Thank you for the insight! >>> >>> How would you go about modeling my problem correctly? >>> >>> The domain constraints are: >>> - there are many "records" >>> - there are 1:1 links between "records", and the links (table) contain = a lot of information about the link (so adding a record_id (fk) to the reco= rds table would also add a ton of columns). >>> - the links are bidirectional >>> - each "record" can be linked with exactly one "record", so a record li= nking to another does not allow the record being referenced to be in any ot= her link either. >>> - a graph of records and their connections (links) must be efficiently = queried / formed >>> >> >> Node: [node_id PK, edge_id {FK edge.edge_id}, {Unique: node_id, edge_id)= ] >> Edge: [edge_id PK, =E2=80=A6] >> Node-Edge: [(node_id, edge_id) {FK node.node_id, node.edge_id}, slot {ch= eck slot in (1,2); not null}, {PK: (edge_id, slot)}, {Unique: node_id}] >> >> That doesn=E2=80=99t enforce =E2=80=9Cnot zero=E2=80=9D or missing recor= ds, which is possible but generally a pain, but does enforce that a node ma= y have at most one edge, and each edge has at most two nodes. >> >> With a deferred not null constraint on node.esge_id I think you can solv= e prevent missing links problem, assuming you always add nodes in pairs.=C2= =A0 You=E2=80=99d do so ething similar with edge.edge_id if you wanted to a= void dangling edges (edges without nodes). >> >> David J. >> >> > > ------=_Part_4910_997264664.1661428055569 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi,

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.

I have written the following schema:

<= br>

-- node

CREATE TABLE = api_endpoints (

id UUID PRIMARY KEY,
<= /div>

api_endoint_edge_id UUID REFERENCES api_endpo= int_edges (id),

UNIQUE (id, api_endpoint_= edge_id), -- did I interpret this correctly?

&nb= sp; ...

);

-- edge

CREATE TA= BLE api_endpoint_edges (

id UUID PRIMARY = KEY,

... -- ton of data

);

-- node-edge, how should i name this table? is just dropping the plura= lization readable?

CREATE TABLE api_endpoint_edg= e (

id UUID PRIMARY KEY,

api_endoint_id UUID REFERENCES api_endpoints (id),

api_endoint_edge_id UUID PRIMARY KEY REFER= ENCES api_endpoints (id),

-- what is= slot?

);

<= div dir=3D"auto">Thank you for helping me get started with Postgres!

=

Aug 24, 2022, 13:47 by david.g.johnston@gm= ail.com:
On Wednesd= ay, August 24, 2022, <awolchute@tutanota.com> wrote= :
= Hi,

Thank you for th= e insight!

How would= you go about modeling my problem correctly?
The domain constraints are:
- there are many "records"
- there are 1:1 = links between "records", and the links (table) contain a lot of information= about the link (so adding a record_id (fk) to the records table would also= add a ton of columns).
- the links are bidirect= ional
- each "record" can be linked with exactly= one "record", so a record linking to another does not allow the record bei= ng referenced to be in any other link either.
- = a graph of records and their connections (links) must be efficiently querie= d / formed

Node: [node_id P= K, edge_id {FK edge.edge_id}, {Unique: node_id, edge_id)]
Edg= e: [edge_id PK, =E2=80=A6]
Node-Edge: [(node_id, edge_id) {FK= node.node_id, node.edge_id}, slot {check slot in (1,2); not null}, {PK: (e= dge_id, slot)}, {Unique: node_id}]

That doesn= =E2=80=99t enforce =E2=80=9Cnot zero=E2=80=9D or missing records, which is = possible but generally a pain, but does enforce that a node may have at mos= t one edge, and each edge has at most two nodes.

With a deferred not null constraint on node.esge_id I think you can solv= e prevent missing links problem, assuming you always add nodes in pairs.&nb= sp; You=E2=80=99d do so ething similar with edge.edge_id if you wanted to a= void dangling edges (edges without nodes).

Dav= id J.

------=_Part_4910_997264664.1661428055569-- From david.g.johnston@gmail.com Sat Jun 6 11:55:30 2026 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--