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
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
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
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
5 votes
2 answers
2k views

SQL Server 2014 Concurrent input issue

In table Orders, I store the orders we receive from all shops. Since an order can have multiple lines, among the columns there are OrderID and OrderLineID where OrderID can be duplicated but ...
Joe's user avatar
  • 359
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
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
3 votes
1 answer
10k views

How can I optimize my mysql setup to create my index faster?

I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored. I have an MyISAM like ...
Dolan Antenucci's user avatar
2 votes
2 answers
6k views

Is unique index better than unique constraint when an index with an operator class is required

Postgres docs say: The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no ...
user4150760's user avatar
  • 1,099
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
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
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
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
4 votes
1 answer
442 views

Can column uniqueness be commutative?

I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be ...
user4150760's user avatar
  • 1,099
3 votes
3 answers
2k views

Unique combination key MySQL

Lets say I have a table with 3 fields: id1 id2 mapTypeId What I want is a primary/unique key that is any combination of id1 and id2. For example if I have this record already in the database: 1, 2,...
ryanzec's user avatar
  • 495
2 votes
1 answer
1k views

In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

Let's assume that I have table data: CREATE TABLE `test`.`data` ( `row_id` INT NOT NULL , `data_id` INT NOT NULL , PRIMARY KEY (`row_id`) ); Now, let's assume that data_id has unique values. Is ...
matt's user avatar
  • 203
1 vote
3 answers
599 views

Pairing Row Values in Any Order

I'm trying to find % of unique pairs that exist in two columns. For example Where 1,6 and 6,1 / 2,3 and 3,2 are unique pairs. So the % of matched pairs is 33%
James's user avatar
  • 21
1 vote
1 answer
1k views

How to have uniqueness constraints for structures across multiple tables?

Say I have a schema system something like this: create table objects { uuid id; string type; } create table object_properties { uuid id; uuid source_id; // the object which has this property ...
Lance's user avatar
  • 209
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
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
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
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
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
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
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
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
5 votes
2 answers
7k views

Unique across tables

I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users). However, each user can also have additional emails. ...
ThiefMaster's user avatar
5 votes
2 answers
3k views

Managing CTE execution order under uncertainty

I am writing a large, multi-step CTE for performance reasons. In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero. In a ...
Jim Bob's user avatar
  • 555
5 votes
1 answer
3k views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
vektor's user avatar
  • 401
4 votes
1 answer
939 views

How to handle uniqueness of many large columns in SQL Server?

I have the following table: CREATE TABLE dbo.Document ( DocumentId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DocumentPRIMARY KEY CLUSTERED, [Timestamp] datetime2(7) NOT NULL CONSTRAINT ...
Alexei's user avatar
  • 1,271
3 votes
1 answer
10k views

How to use ON DUPLICATE KEY for UPDATE

Consider a table of CREATE TABLE test ( id int(11) unsigned NOT NULL AUTO_INCREMENT, external_id int(11), number smallint(5), value varchar(255), UNIQUE INDEX (external_id, number), PRIMARY KEY(id) )...
Googlebot's user avatar
  • 4,531
3 votes
1 answer
656 views

Can an Oracle IDENTITY column be nullable?

I want to create an ID column that automatically generates unique ID numbers by default. Users won't be inserting rows via INSERT statements, rather, they'll create rows using an out-of-box ...
User1974's user avatar
  • 1,477
3 votes
1 answer
4k views

Constraint for a unique column if other column has same value

I'm wondering if it's possible to have a column be unique for each value of another column in MySQL. What I'm wanting to have is the same functionality as what a GROUP BY would do. Here's the table: ...
SameOldNick's user avatar
3 votes
1 answer
1k views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows,...
Apeiron's user avatar
  • 191
2 votes
1 answer
2k views

How to reference a UNIQUE KEY CONSTRAINT in parent table as a foreign key in child table?

I have created two tables, the parent in this case being "department" and "employee" being child. CREATE TABLE department ( department_id NUMBER(4) CONSTRAINT department_id_not_null NOT NULL, ...
Tuonelan Joutsen's user avatar
1 vote
2 answers
426 views

Best type to use in a composite key when one of the values varies in type

I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table BlogId Url ...
casperOne's user avatar
  • 301
1 vote
1 answer
517 views

How to enforce a column value on duplicate values on postgresql using constraint?

I have the following table: mytable --------- id Serial PK action Integer model VARCHAR model_id integer active BOOL But due to my logic there cannot be duplicate values having the same values for ...
Dimitrios Desyllas's user avatar
1 vote
2 answers
4k views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
nali's user avatar
  • 196
1 vote
1 answer
2k views

SQL Server unique index, per subset of data, based on values of another column

Say I have a Customers table, and an Emails table. Emails table has these fields: Id CustomerId EmailValue Now, I want to create a constraint so that no duplicate emails should be ...
Saeed Neamati's user avatar
1 vote
3 answers
2k views

Auto-incremented ID with sequence and trigger: How to handle manual overrides (causing non-unique error)

I have a sequence and trigger that provide auto-incrementing IDs to a PROJECT_ID column: PROJECTS +------------+-----+ | PROJECT_ID | ... | +------------+-----+ | LC000001 | | | LC000002 | ...
User1974's user avatar
  • 1,477
0 votes
1 answer
854 views

Error "UNIQUE constraint failed" with composite primary key

With littele SQL(ite) practice, I tried to create a table with a composite primary key: CREATE TABLE NAMES ( TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20), PRIMARY KEY (TYPE, ...
U. Windl's user avatar
  • 125
-4 votes
1 answer
210 views

How do I ensure a relationship is commutative and unique? [closed]

In PostgreSQL, we can ensure the relations stored inside a table a commutative-ly (or bidirectional etc) unique. For instance, CREATE TABLE foo ( a int, b int, UNIQUE ( greatest(a,b), least(a,b)...
Evan Carroll's user avatar
  • 63.6k