‍

Unique Indexes With Some Rows Excluded

‍

Learn much more about indexes

‍

I've written an entire book to learn everything about crafting good indexes. Instead of boring walls of text, everything is explained by easy-to-understand graphics. And I am right now offering a 50% black friday deal. More info at the end of this newsletter.

‍

Unique indexes are used to ensure that some values or combinations of values are unique. However, they only work when the uniqueness constraint should be enforced on all table rows because it can not be limited to ignore e.g. soft-deleted ones. But with partial indexes, supported by PostgreSQL, this is possible because a WHERE condition can control the included rows of the index. With MySQL, the behaviour to exclude soft-deleted rows in the uniqueness constraint needs to be emulated.

‍

PostgreSQL

‍

CREATE UNIQUE INDEX users_email_uniq ON users (
  email
) WHERE deleted_at IS NULL;

‍

MySQL

‍

CREATE UNIQUE INDEX users_email_uniq ON users (
  email, 
  (IF(deleted_at,  NULL,  1))
);

‍

50% OFF To Learn Everything About Database Indexes —
You don't have to know all the complicated database internals to make a slow query fast. This book teaches simple rules for creating good indexes - enriched with dozens of illustrations to make all concepts more easy to understand. You have no excuse anymore for your slow queries!

SQL For Devs SPONSOR

‍

This message was sent to aaa1@niepodam.pl.
If you don't want to receive these emails in the future, you can unsubscribe .

‍