Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfKOS-001SCp-2U for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 14:59:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfKOR-004BMS-1j for pgsql-general@arkaria.postgresql.org; Tue, 04 Feb 2025 14:59:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tfKOQ-004BMJ-Mt for pgsql-general@lists.postgresql.org; Tue, 04 Feb 2025 14:59:14 +0000 Received: from mail-oi1-x22e.google.com ([2607:f8b0:4864:20::22e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tfKON-003FXz-31 for pgsql-general@postgresql.org; Tue, 04 Feb 2025 14:59:14 +0000 Received: by mail-oi1-x22e.google.com with SMTP id 5614622812f47-3eb7e725aa0so2212549b6e.0 for ; Tue, 04 Feb 2025 06:59:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738681151; x=1739285951; darn=postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ft7VwnaXmb3q4yG6Y89XgKnIexQeCUWGC3qXO+GoeTg=; b=Z25HjFGxp4hKyVbXWVTGeoeBf5OxUZj2AWXOAXyQb2udMODKGs5+xmbnnSI/xKXh5N MI0jv3OUmOf21pOgkOArkCgTXJLAJi5MAGPp7tTBbX9mCf8oCwxOGIg96qxx5G4fZKvV 0Xh2Pgk7irMAbY6xffeBghj79laUApkbZMSVuddfu5+nPlgkBb+YD5fDtiP5qnTImAd2 7mHilrEuTwxQF5qe+JZ8n+V/G0QN3xjLvwDIr69EucKxq1NLgqrDrj1yVkx8ZM9Y4o6p cmz3nLodcDDfLUdPPrFWAhZHaNi9lW+yyOhMXJ2lHmzVWyWxn5vqfe1QD7NInFvmSwBv meBA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738681151; x=1739285951; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ft7VwnaXmb3q4yG6Y89XgKnIexQeCUWGC3qXO+GoeTg=; b=C2PoESlz2iRvFRGEakr5R5PsSung1iSNDkmrggCIeIxOREJa4N59c0eS8e/V2r6JCH l515fRZmCeo5sg2fzctBmWUp+Q9iK1ODFrs/BpS7AXdIzc5/kqlvQnhNm/nYAxLs+NWT oxlo2Qg3QddbtfscUGTLWlKY2TLZJ6u49pxYcVEzSXi8xK/i8nn4Eu1++1LCmnrQkxgd K3k4kMiXBw89E0ynZP3CjASt7QaZ4TzL68WZGZZjLUAQ2XWuiSqnWa560bfObGWdOGOa OqAxcrwju0d7wPQrEzQDs6lfRDop16YrFAsoH11xeTLu5SoSQUqzKGZmsWYnANRBoTFJ +eWQ== X-Gm-Message-State: AOJu0YwhOrpfxJ3P4os5VEWIM4/KSWt0u/cIiuYWZRT1MMnNDfb5765E /JEuuMFWiUmi+GLKJq3EedlejEhrpzbcylHYd8cZqbLnNamYmdgCEzk4PZnKQYkQpCpY+WhHO3a h9YpXO5yPVNVERIBsc+8+cbu85Y7rUw== X-Gm-Gg: ASbGncssWhVNGof4P5Cp+Y9zscKwjoJtr3h4Rs4fbPd8JTGtuJiLRu8I0iEwvpcxxQH ZhdS50pbdSR6zVTXghYdWwosXLZMtYZu7mM8cd2tkDyDunnFRvnYZTnN5AW5jmW7h2uPhemE= X-Google-Smtp-Source: AGHT+IEq98hquX3Q4ttrMibf9kQIh1e5LjEgANGR5RqqNv4deFEbdEkiZ1TgmnsaAkIG+lvzNG/2eeGEbehrpAokEXM= X-Received: by 2002:a05:6808:3c44:b0:3f1:b0db:3fa3 with SMTP id 5614622812f47-3f3237e6ebdmr18087676b6e.0.1738681150516; Tue, 04 Feb 2025 06:59:10 -0800 (PST) MIME-Version: 1.0 Received: by 2002:a05:6802:a0c:b0:577:9519:f64a with HTTP; Tue, 4 Feb 2025 06:59:09 -0800 (PST) In-Reply-To: References: <4e7a338-c7d3-e944-20bd-a6e346e175c7@appl-ecosys.com> From: "David G. Johnston" Date: Tue, 4 Feb 2025 07:59:09 -0700 X-Gm-Features: AWEUYZnOmgzVlJbadgCuE7MJkaBdJtuwGwo3gfDNujX2DfI-NBZ1K-saqxK4JGk Message-ID: Subject: Re: Lookup tables To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000005ab571062d524001" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005ab571062d524001 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tuesday, February 4, 2025, Ron Johnson wrote: > On Tue, Feb 4, 2025 at 9:41=E2=80=AFAM David G. Johnston < > david.g.johnston@gmail.com> wrote: > >> On Tuesday, February 4, 2025, Rich Shepard >> wrote: >> >>> Should lookup tables have a numeric FK column as well as the descriptio= n >>> column? >>> >>> If so, how should I add an FK to the two lookup tables in my database? >>> >> >> Most do (have a surrogate PK) since it removes cascading updates >> > > How does a synthetic PK "remove cascading updates"? Doesn't the decision > on whether or not to cascade update depend on the ON UPDATE CASCADE claus= e > of the FK definition? > > People don=E2=80=99t change synthetic PKs so updates never have to happen o= n the FK side. Labels do get changed, in which case you have to update the FK label side. David J. --0000000000005ab571062d524001 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tuesday, February 4, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Tue, Feb 4, 2025 at 9:41= =E2=80=AFAM David G. Johnston <david.g.johnston@gmail.com> wrote:
On = Tuesday, February 4, 2025, Rich Shepard <rshepard@appl-ecosys.com> wrote:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">Should lookup tables have = a numeric FK column as well as the description column?

If so, how should I add an FK to the two lookup tables in my database?

Most do (have a surrogate PK) since it rem= oves cascading updates
=C2=A0
How do= es a synthetic PK "remove cascading updates"?=C2=A0 Doesn't t= he decision on whether or not to cascade update depend on the ON UPDATE CAS= CADE clause of the FK definition?


People don=E2=80=99t change synthetic PKs so updates neve= r have to happen on the FK side.=C2=A0 Labels do get changed, in which case= you have to update the FK label side.

David J.
=C2=A0
--0000000000005ab571062d524001--