Message-ID: <f68465771b664815c47d50bad814db2ba4e4e9ba.camel@cybertec.at>
Subject: Re: Clarification of behaviour when dropping partitions
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Bolaji Wahab <bolajiwahab23@gmail.com>
Cc: pgsql-general@postgresql.org
Date: Thu, 05 Dec 2024 02:38:34 +0100
In-Reply-To: <CA+f_mLO=qNkHCumSfbcLz7Fb7DBoByNLLby3NDUod_TuHn1ZeA@mail.gmail.com>
References: 
	<CA+f_mLPhCa_G3nRrB4=e3trqCSQ88x56ZvfeweH-AhwS77xEGg@mail.gmail.com>
	 <928c165be24ee307c2fbaa29a2c6360eb8a00e02.camel@cybertec.at>
	 <CA+f_mLO=qNkHCumSfbcLz7Fb7DBoByNLLby3NDUod_TuHn1ZeA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-2.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/f68465771b664815c47d50bad814db2ba4e4e9ba.camel%40cybertec.at>
Precedence: bulk

On Wed, 2024-12-04 at 23:00 +0100, Bolaji Wahab wrote:
> On Wed, Dec 4, 2024 at 6:20=E2=80=AFPM Laurenz Albe <laurenz.albe@cyberte=
c.at> wrote:
> > On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> > > I have these two partitioned tables, with=C2=A0referential integrity.=
 The tables
> > > are structured=C2=A0in such=C2=A0a way that we have 1 to 1 mapping be=
tween their
> > > partitions. This is achieved with a foreign key.
> >=20
> > I recommend that you don't create the foreign key constraint between th=
e
> > partitioned tables, but between the individual partitions.
> >=20
> > That will make detaching and dropping partitions easier, and you will h=
ave
> > the same integrity guarantees.
>=20
> Yes, this is what I have done.
> But the whole point of declaring the foreign key constraint on the partit=
ioned
> table is to have it automatically created on subsequent/future partitions=
.

Sure, but then you have to accept the disadvantage that it becomes more
difficult to detach partitions.  I think it is less pain to create the
constraint on the partition level.

Yours,
Laurenz Albe