Re: Dropping behavior for unique CONSTRAINTs

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"