Skip to main content
Share Your Experience: Take the 2024 Developer Survey
39 votes
Accepted

Creating a UNIQUE constraint from a JSON object

First and foremost: I agree with both the comments of @a_horse_with_no_name and @dezso: you should normalize your data. JSON is not for that. However, if some reason I cannot fathom really makes ...
joanolo's user avatar
  • 13.5k
34 votes
Accepted

PostgreSQL UPSERT issue with NULL values

Clarify ON CONFLICT DO UPDATE behavior Consider the manual here: For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by ...
Erwin Brandstetter's user avatar
25 votes
Accepted

Deferrable unique index in postgres

A index cannot be deferred - doesn't matter if it is UNIQUE or not, partial or not, only a UNIQUE constraint. Other types of constraints (FOREIGN KEY, PRIMARY KEY, EXCLUDE) are also deferrable - but ...
ypercubeᵀᴹ's user avatar
24 votes

Why doesn't my UNIQUE constraint trigger?

NULL is the culprit, because two NULL values are considered distinct in a UNIQUE constraint - in accordance with the SQL standard. Postgres 15 or newer Postgres 15 adds an option to change this ...
Erwin Brandstetter's user avatar
21 votes
Accepted

Does creating a unique constraint on a Postgres column remove the need to index it?

Yes. A UNIQUE constraint is implemented with the help of a unique index - a b-tree index with default ascending sort ordering over all involved columns. The index is created and maintained ...
Erwin Brandstetter's user avatar
16 votes
Accepted

Unique index updates and statistics row modification counters

SQL Server always uses the Split, Sort, and Collapse combination of operators when maintaining a unique index as part of an update that affects (or might affect) more than one row. Working through the ...
Paul White's user avatar
  • 85.9k
16 votes
Accepted

Index Uniqueness Overhead

I'm frequently involved in code reviews for the dev team, and I need to be able to give general guidelines for them to follow. The environment I'm currently involved in has 250 servers with 2500 ...
Hannah Vernon's user avatar
  • 70.1k
15 votes
Accepted

Why do composite foreign keys need a separate unique constraint?

It's a limitation of the DBMS - in all of them as far as I know. And not only when adding a column but also when rearranging columns. If we have a UNIQUE constraint on (a1, a2), we can't add a FOREIGN ...
ypercubeᵀᴹ's user avatar
14 votes

When should I use a unique constraint instead of a unique index?

To quote MSDN as an authoritative source: There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation ...
tinonetic's user avatar
  • 240
14 votes
Accepted

mysql - unique index with nulls - actual solution anyone?

[This is not tested as it requires MySQL 5.7.6 or above] While I agree with the comments above, I still have an idea that can be tried, which I think is not the best in terms of performance, but it ...
Jehad Keriaki's user avatar
14 votes

How to create a unique index with only certain values required to be unique?

In versions MySQL 5.7 and MariaDB 5.2+ you can use a (generated) VIRTUAL column to accomplish this. You defineucode as a virtual column and then add a UNIQUE constraint: CREATE TABLE codes ( ...
ypercubeᵀᴹ's user avatar
14 votes
Accepted

How to generate globally unique ids for different tables of the same database?

Back in the day we had an ID table. Single column, single row with an int value. Every transaction first updated that table to get a new value, which was then used wherever it was needed. This was, of ...
Michael Green's user avatar
13 votes
Accepted

Is there any way to have Unique Constraint and NOT have Unique Index?

Reasonable Answer: No. You really should just let it create the unique index and let it do it's job. Space is cheap. If you make the column you want unique the clustered index key, then it won't ...
Jonathan Fite's user avatar
12 votes

Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

I would use my own identifier. The species name may be unique but - it's too long - it's a string For example, in SQL Server, if used as the clustered primary key it will be used in non-clustered ...
gbn's user avatar
  • 69.9k
11 votes
Accepted

When to use unique composite keys?

If the intention is to build a relational database, then the objective should be to reflect the informational characteristics of the relevant business environment in the database layout, so the short ...
MDCCL's user avatar
  • 8,520
11 votes
Accepted

N'Șc' considered duplicate key of N'C' using Latin1_General_CI_AS collation

The problem is that the old SQL Server Collations (i.e. those with names starting with SQL_) and the first two versions of the Windows Collations (the 80 series which came with SQL Server 2000 and do ...
Solomon Rutzky's user avatar
10 votes

DynamoDB - Multiple range keys

Put a copy of B and C in the range field (as an extra column) and make that your range key like "B_C" and you can still also have separate columns for B and C if you need.
Jeff M's user avatar
  • 254
10 votes
Accepted

Lookup performance of Numeric vs String

Hate to be captain obvious on this one, but Instagram generously provides a function that you linked to that stores the keys as bigint. CREATE SCHEMA insta5; CREATE SEQUENCE insta5.table_id_seq; ...
Evan Carroll's user avatar
  • 63.6k
10 votes
Accepted

Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

As a former environmental scientist with a bit of "bugs and bunnies" background (fish and inverts specifically), I would recommend the use your own identifier. As a database administrator, you have ...
Zerodf's user avatar
  • 333
10 votes
Accepted

When should the IGNORE_DUP_KEY option be used on an index?

It's definitely an atypical setting to enable for an index. I don't think you're going to find many people who jump up and talk about using it. Of course, there are a couple helpful posts on the ...
Erik Darling's user avatar
  • 41.2k
10 votes

Why doesn't my UNIQUE constraint trigger?

Your problem stems from the NULL values. From the documentation (emphasis added) In general, a unique constraint is violated if there is more than one row in the table where the values of all of the ...
gwaigh's user avatar
  • 259
9 votes

Multiple primary keys in PostgreSQL

You can define a table like this: CREATE TABLE mytable ( field1 INTEGER, field2 INTEGER, PRIMARY KEY (field1, field2) );
Demitri's user avatar
  • 211
9 votes

PostgreSQL UPSERT issue with NULL values

I think the problem is that you don't have a partial index and the ON CONFLICT syntax doesn't match the test_upsert_upsert_id_idx index but the other unique constraint. If you define the index as ...
ypercubeᵀᴹ's user avatar
9 votes

Why do composite foreign keys need a separate unique constraint?

Foreign Keys in general (not just composite) MUST point to a UNIQUE KEY of some sort in another table. If they did not, there would be no relational data integrity. This is complaining because, ...
Joishi Bodio's user avatar
  • 3,388
9 votes

Allow only unique combination of values for a composite primary key

Since you use MariaDB 10.2, you can add a CHECK constraint, enforcing that the first id is less than the second (id_1 < id_2). CHECK constraints were added in version 10.2.1. This has the ...
ypercubeᵀᴹ's user avatar
9 votes

Does the concept of candidate key exist only in theory?

As far as I know, no SQL database management system (DBMS) supplies the CANDIDATE KEY keyword as such, but (as I consider that you are suggesting in the question) that does not mean that the notion (...
MDCCL's user avatar
  • 8,520
9 votes

How to generate globally unique ids for different tables of the same database?

For unique ID values in the same table, I presume you are aware of the commonly used IDENTITY option, usually using a 32-bit value starting from 1 (so for defining a PK this way something like ID INT ...
David Spillett's user avatar
9 votes
Accepted

Does a primary key contain exactly one Null value...?

No attribute that is part of a key can contain nulls; if a column is nullable then it isn't part of any key. It is also the case that the set of columns defined by a UNIQUE or PRIMARY KEY constraint ...
nvogel's user avatar
  • 3,767
8 votes

How to create a Unique (Index) constraint across multiple tables?

You could use a sequence for generating the values for ID fields. The documentation explicitly mentions your use case. The example C on said page has a sample implementation about this. Use ...
vonPryz's user avatar
  • 3,081

Only top scored, non community-wiki answers of a minimum length are eligible