pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Dropping behavior for unique CONSTRAINTs
Date: Sat, 4 Mar 2023 12:51:52 +0100
Message-ID: <20230304115152.df5pmrgny7z5ogkm@hjp.at> (raw)
In-Reply-To: <0d0e4771-a1f2-f883-a983-4a4aee1bce9b@gmail.com>
References: <3d7d49f0-7eef-4d6d-8dc3-1993ba2fc2cb@me.com>
	<20230304080303.a3oimbvcoo5lo3d5@hjp.at>
	<0d0e4771-a1f2-f883-a983-4a4aee1bce9b@gmail.com>

On 2023-03-04 02:34:02 -0600, Ron wrote:
> On 3/4/23 02:03, Peter J. Holzer wrote:
> [snip]
> > So your plan is to create a unique constraint (backed by a unique
> > index) and then to drop the index and keep the constraint?
> > 
> > That doesn't work. A unique constraint can't exist without a (unique)
> > index. Think about it: With a unique constraint PostgreSQL needs to
> > check for every insert whether the value already exists in the table.
> > Without an index this would mean a full table scan.
> 
> I cut my teeth on an RDBMS which didn't automagically create a backing
> index.  You had to do it yourself...

Just curious: Which RDBMS was that?

I'm pretty sure Oracle did that automatically when I first used it
(version 8.0.5). Not sure about MySQL, but if it didn't have an index it
probably didn't enfocre the constraint either (it definitely didn't
enforce foreign key constraints).

Speaking of foreign key constraints: Neither Oracle nor PostgreSQL
automatically add an index on a foreign key. That bit me hard back in
the day ...

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (10+ messages)  latest in thread

Message-ID: <20230304115152.df5pmrgny7z5ogkm@hjp.at>
Permalink:  ../20230304115152.df5pmrgny7z5ogkm@hjp.at/
Also on:    postgresql.org/message-id/20230304115152.df5pmrgny7z5ogkm@hjp.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: hjp-pgsql@hjp.at
  Subject: Re: Dropping behavior for unique CONSTRAINTs
  In-Reply-To: <20230304115152.df5pmrgny7z5ogkm@hjp.at>
  References: <3d7d49f0-7eef-4d6d-8dc3-1993ba2fc2cb@me.com> <20230304080303.a3oimbvcoo5lo3d5@hjp.at> <0d0e4771-a1f2-f883-a983-4a4aee1bce9b@gmail.com> <20230304115152.df5pmrgny7z5ogkm@hjp.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox