33
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
...
- 170k
33
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 ...
- 13.2k
24
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 ...
- 96.8k
22
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 ...
- 170k
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 ...
- 170k
18
votes
Why does this UPDATE fail with a unique key constraint violation?
It boils down to what UPDATE statement does. It's not entirely obvious but your statement is equivalent to this one:
UPDATE upd SET
Ticket = 'ARP.ExGE'
, Method ...
- 96.8k
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 ...
- 82.1k
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 ...
- 69.5k
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 ...
- 240
14
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 ...
- 96.8k
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 ...
- 24.6k
13
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 ...
- 3,051
13
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
( ...
- 96.8k
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 ...
- 8,571
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 ...
- 69.6k
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 ...
- 8,430
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 ...
- 68.1k
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.
- 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;
...
- 61.4k
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 ...
- 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 ...
- 38.2k
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)
);
- 211
9
votes
Accepted
DynamoDB - Multiple range keys
You can't have more than 2 fields as primary key in DynamoDB.
As a workaround you can create local secondary index for each field that you want to be a range key. But you will not be able to build ...
- 206
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 ...
- 96.8k
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 ...
- 96.8k
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 (...
- 8,430
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 ...
- 31.7k
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 ...
- 3,767
8
votes
Accepted
How to name table-variable function unique constraint?
Table Variables, whether created from a DECLARE or from a RETURNS in a CREATE FUNCTION statement, as well as User-Defined Table Types (UDTTs), do not allow for constraints to be named. According to ...
- 68.1k
Only top scored, non community-wiki answers of a minimum length are eligible