Skip to main content

Questions tagged [unique-constraint]

DDL UNIQUE constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. The data contained in the column or columns involved is, therefore, useful to uniquely identify a row in the relevant table.

Filter by
Sorted by
Tagged with
279 votes
7 answers
154k views

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

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
bernd_k's user avatar
  • 12.3k
157 votes
3 answers
173k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
Manuel Leduc's user avatar
  • 1,681
42 votes
5 answers
62k views

Why does a UNIQUE constraint allow only one NULL in SQL Server?

In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should ...
user87166's user avatar
  • 563
36 votes
1 answer
104k views

Optimal way to ignore duplicate inserts? [duplicate]

Background This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code: -- Ignores duplicates. INSERT INTO db_table (...
Dave Jarvis's user avatar
33 votes
2 answers
17k views

Custom unique constraint, only enforced if one column has a specific value

Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is 'true', then I want the ...
Faheem Mitha's user avatar
  • 1,039
31 votes
2 answers
31k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
Shaun McCready's user avatar
30 votes
2 answers
120k views

Multiple primary keys in PostgreSQL [closed]

I have the following table: CREATE TABLE word( word CHARACTER VARYING NOT NULL, id BIGINT NOT NULL, repeat INTEGER NOT NULL ); ALTER TABLE public.word OWNER TO postgres; ALTER TABLE ONLY word ADD ...
mostafa's user avatar
  • 319
26 votes
3 answers
122k views

What is the difference between a primary key and a superkey in a DBMS

What is the difference between a primary key and a superkey in a DBMS? Can a primary key and a superkey both have multiple columns? Is a primary key a subset of a superkey or vice versa?
MikeHil's user avatar
  • 369
22 votes
1 answer
24k views

Creating a UNIQUE constraint from a JSON object

Lets take some example table peoples , that got only 2 fields: id and data(json). SELECT data FROM peoples ; {"name": "Adam","pos":"DBA","age":22 } {"name": "Alice","pos":"Security","age":33 } {"name"...
Chenko47's user avatar
  • 351
22 votes
2 answers
17k views

Deferrable unique index in postgres

Looking into postgres documentation for alter table, it seems regular constrains can be marked as DEFERRABLE (more concretely, INITIALLY DEFERRED, which is what I'm interested in). Indexes can also ...
jcristovao's user avatar
20 votes
4 answers
24k views

Differences between "Unique Key" and "Primary Key"

What are the major differences between Unique Key and Primary Key in MySQL?
newuser's user avatar
  • 681
19 votes
2 answers
11k views

Avoid unique violation in atomic transaction

Is possible to create atomic transaction in PostgreSQL? Consider I have table category with these rows: id|name --|--------- 1 |'tablets' 2 |'phones' And column name has unique constraint. If I ...
Petr Přikryl's user avatar
19 votes
4 answers
38k views

NVARCHAR column as PRIMARY KEY or as UNIQUE column

I'm developing a SQL Server 2012 database and I have a doubt about nvarchar columns as primary keys. I have this table: CREATE TABLE [dbo].[CODES] ( [ID_CODE] [bigint] IDENTITY(1,1) NOT NULL, ...
VansFannel's user avatar
  • 1,863
17 votes
2 answers
20k views

Why do composite foreign keys need a separate unique constraint?

Here is a simple table where records may reference parent records in the same table: CREATE TABLE foo ( id SERIAL PRIMARY KEY, parent_id INT NULL, num INT NOT ...
Zilk's user avatar
  • 1,121
16 votes
1 answer
8k views

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

Does creating a unique constraint on a Postgres column remove the need to index it? I expect that an index is automatically needed to maintain the constraint efficiently.
vfclists's user avatar
  • 1,053
15 votes
1 answer
48k views

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now the ...
Staeff's user avatar
  • 407
15 votes
4 answers
2k views

Index Uniqueness Overhead

I've been having an ongoing debate with various developers in my office on the cost of an index, and whether or not uniqueness is beneficial or costly (probably both). The crux of the issue is our ...
Solonotix's user avatar
  • 253
14 votes
4 answers
3k views

Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value ...
dsaxton's user avatar
  • 257
14 votes
4 answers
11k views

Why doesn't my UNIQUE constraint trigger?

I have this UNIQUE constraint: ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE ("col1", "col2", "col3", "col4", "col5", "col6", &...
user16508174's user avatar
14 votes
1 answer
888 views

Unique index updates and statistics row modification counters

Given the following table, unique clustered index, and statistics: CREATE TABLE dbo.Banana ( pk integer NOT NULL, c1 char(1) NOT NULL, c2 char(1) NOT NULL ); CREATE UNIQUE CLUSTERED ...
Paul White's user avatar
  • 86.3k
12 votes
5 answers
14k views

Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem. Running MS SQL Server 2012. The problem is with this UPDATE statement: UPDATE dbo.tAccts SET Ticket ...
ArleyD's user avatar
  • 121
11 votes
3 answers
6k views

Lookup performance of Numeric vs String

I'm working on a project that is using the instagram key format. TL;DR 64-bit integer ids. They'll be used for lookups, and we also like them for sorting and batching since they will naturally sort ...
ChrisJ's user avatar
  • 591
11 votes
2 answers
32k views

DynamoDB - Multiple range keys

Is it possible to have multiple fields as range key? Say I have a table where each row is uniquely identified by <A,B,C> ------------------------------- A | B | C | D | E | ----...
Thiyagu's user avatar
  • 213
11 votes
3 answers
11k views

Email address unique or primary key?

I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in ...
aandis's user avatar
  • 235
11 votes
1 answer
18k views

Postgresql 9.3: Add Unique constraint using an existing unique index

I would like to add a unique constraint to a DB field in one of my production databases. Unfortunately, the following statement is blocking: ALTER TABLE package ADD CONSTRAINT unique_package_id ...
Adam Matan's user avatar
  • 11.7k
11 votes
1 answer
402 views

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

I have a table with a unique key that includes an NVARCHAR(50) column (correct or not, but is there). So, when trying to insert Șc or C (doesn't matter the order of the insert) it breaks on the 2nd ...
Yaroslav's user avatar
  • 2,828
11 votes
3 answers
10k views

Does making a field unique make it indexed?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly ...
corsiKa's user avatar
  • 213
10 votes
2 answers
8k views

UNIQUE constraint on large VARCHARs - PostgreSQL

I have a column defined like: data_url character varying(32768) NOT NULL and UNIQUE constraint on that column: CONSTRAINT unique_data_url UNIQUE (data_url) When a large object is being inserted ...
czerny's user avatar
  • 523
10 votes
4 answers
902 views

What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?

I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind: Simplicity; there are no ...
Jon of All Trades's user avatar
10 votes
2 answers
19k views

How to use Unique key via combinations of table fields?

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1 CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, ...
Jake Wilson's user avatar
  • 2,437
10 votes
1 answer
22k views

mysql - unique index with nulls - actual solution anyone?

CREATE TABLE IF NOT EXISTS b2c_constants ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, is_deleted BOOL DEFAULT FALSE, UNIQUE (name) ) ENGINE InnoDB ...
jurchiks's user avatar
  • 203
10 votes
1 answer
1k views

Best way to model the relationship of unique pairs

I have two tables; one for storing thing and one for storing the relationship between two thing objects. dbfiddle example Assume: AB == BA. Storing both would be redundant A != B. The ...
h3rrmiller's user avatar
9 votes
3 answers
15k views

How does unique key help to improve SQL query performance?

Suppose I have a table with an id column and and I am maintaining unique values manually while inserting and updating records instead of creating a unique key on that column (e.g. by using a date &...
Shell's user avatar
  • 193
9 votes
3 answers
2k views

Is there a performance penalty when there are two unique indexes on a table with all of the colums in one index are also in the other index?

In my database I have a table with two indexes. There is one unique index #1 on columns a,b which I need for data integrity reasons. I have another index #2 with columns c,a,b that is used for ...
bigchief's user avatar
  • 225
9 votes
3 answers
2k views

Is it reasonable to mark all columns but one as primary key?

I have a table representing movies. The fields are: id (PK), title, genre, runtime, released_in, tags, origin, downloads. My database cannot be polluted by duplicated rows, so I want to enforce ...
rubik's user avatar
  • 525
9 votes
0 answers
712 views

Inconsistent equality checking of Unicode text in Postgres 9.5?

Using PostgreSQL 9.5. It seems like Postgres is being inconsistent with how it compares strings similar to لى. A unique constraint is considering some strings equal that GROUP BY is considering ...
sudo's user avatar
  • 1,011
8 votes
1 answer
2k views

How to name table-variable function unique constraint?

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as ...
gotqn's user avatar
  • 4,026
8 votes
4 answers
2k views

How to enforce uniqueness of compound primary key made up of two foreign keys to the same table?

I have two tables: a user table and a friendship table. Say the friendship table looks like this: friendship ------------ user_one_id user_two_id other_fields I need to enforce the uniqueness of the ...
maniciam's user avatar
  • 235
8 votes
2 answers
11k views

Partial unique constraint spanning multiple tables in postgres

To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so: CREATE TABLE possession ( possession_id serial ...
lpd's user avatar
  • 251
7 votes
3 answers
10k views

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

In a production system on SQL Server all ids (mostly PKs) in all tables are generated automatically and I am informed that they are unique globally. I mean no 2 ids are the same in the database, even ...
igelr's user avatar
  • 2,132
7 votes
3 answers
297 views

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

Scientific names are well accepted unique identifiers of species. However, scientific names have special characters such as spaces and dots. Can it be a good unique identifier for databasing or should ...
Abel Melquiades Callejo's user avatar
7 votes
3 answers
4k views

When should the IGNORE_DUP_KEY option be used on an index?

Someone said it's preferable to craft your queries to avoid duplicate key exceptions, but I'm not convinced that's more performant that just setting IGNORE_DUP_KEY = ON for the index. My goal is to ...
Triynko's user avatar
  • 615
7 votes
1 answer
3k views

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

I have a list of codes being stored in a table, and some codes should be unique, others can be repeated. The codes should be unique-scoped to another field, product_id. Assuming the code 11 should ...
Zabba's user avatar
  • 221
7 votes
2 answers
1k views

Does the concept of candidate key exist only in theory?

I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate ...
RajS's user avatar
  • 249
7 votes
1 answer
12k views

When to use unique composite keys?

When creating a database structure, I tend to create unique composite keys for every group of data that needs to be unique. Beside them I usually use a primary key (usually INT AI id), unless a ...
Freya's user avatar
  • 173
7 votes
5 answers
7k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too (...
Googlebot's user avatar
  • 4,531
7 votes
3 answers
33k views

Always check with SELECT before INSERT?

If I want to INSERT a row only if the primary key doesn't yet exist, is it more efficient/simpler to execute INSERT directly and ignore the error in the case of duplication, or should i always run ...
James's user avatar
  • 111
7 votes
3 answers
4k views

How can I enforce ensure uniqueness of an association with a time dimension?

I have a database table AB which tracks the association of A and B over time: create table AB columns ( ARef ..., -- FK of A BRef ..., -- FK of B Since as Timestamp, Until as Timestamp, ...
David Bullock's user avatar
7 votes
1 answer
29k views

Deadlock found when trying to get lock; try restarting transaction in mysql

I have a table structure as given below, this table is having 1 million records and having FK relationship with two and three tables having records also in millions. I need to do bulk insert into this ...
Jatin Seth's user avatar
6 votes
1 answer
2k views

Allow only unique combination of values for a composite primary key

I created the following table called books: CREATE TABLE IF NOT EXISTS `books` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `name` TEXT NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=...
Michael Samuel's user avatar

1
2 3 4 5
7