Is there an article on how the unique constraint is implemented & its performance? I'm sorta new to DBMS & verifying that constraint row-by-row seems hugely impractical, so I think it uses something like indexing or hashing. I can't seem to find a resource talking about it though.

@ChocolateOverflow it may depend on which DBMS you use. For SQL Server it uses a BTree index. This is an ordered list of values. So it is efficient to find where a new row should go. If there is already a row with that key you have a duplicate and an exception is thrown. This would work just as well with any other index type. I imagine most of the major RDBMS implement the same way.

Lots of good points here dba.stackexchange.com/questions/317762/…

indeed, Postgres and MySQL use btree indexes, too, for enforcing UNIQUE constraint

The details of btree (or btree+) implementations may differ from DBMS to DBMS but the general idea is the same.

I wonder if there is any DBMS that doesn't use a b-tree index to enforce unique constraints. (I know there are some that don't enforce them at all, so these are the obvious though incorrect answer.)

@JoshDarnell I wasn't aware of venmo. What's the benefit of having extra service for payments instead of just sending requests via bank account?

@mustaccio in Oracle, if you create a UNIQUE constraint, it does NOT necessarily create a unique index as well.

@Zikato bank account requests are more complicated, at least here in the US. With services like Venmo, you don't need to know all the account details / etc. Just a username.

It's convenient in a lot of scenarios. When my band started accepting tips via Venmo, our tips per gig went up by 3-5x.

@ypercubeᵀᴹ you sure?

> To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle Database creates an index on the columns of the unique or primary key in the schema containing the table.

it creates an index if there is not one already.

if there is a non-unique index, it might use that.

if the UNIQUE constraint is deferrable (and there is no index), it also creates a non-unique index.

It does need an index to be there (or it creates one). But the index doesn't have to be unique

It sounds really strange. However, I never mentioned anything about "unique", so I'm not sure whether your statements are meant to support or refute what I said.

@mustaccio I was replying to your question "you sure?"

Morning

well sort of....