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.
42
questions
157
votes
3
answers
170k
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),
...
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 (...
277
votes
7
answers
150k
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 ...
33
votes
2
answers
16k
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 ...
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 ...
31
votes
2
answers
30k
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 ...
14
votes
4
answers
10k
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", &...
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 ...
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 ...
42
votes
5
answers
61k
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 ...
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.
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 ...
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 ...
4
votes
1
answer
438
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 ...
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,...
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 ...
1
vote
3
answers
591
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%
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
...
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?
15
votes
1
answer
47k
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 ...
14
votes
1
answer
886
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 ...
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 ...
7
votes
3
answers
3k
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,
...
7
votes
1
answer
12k
views
When to use unique composite keys? [closed]
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 ...
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 (...
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 ...
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 ...
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 ...
5
votes
2
answers
6k
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.
...
4
votes
1
answer
932
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 ...
3
votes
1
answer
608
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 ...
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)
)...
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:
...
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,...
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, ...
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 ...
1
vote
2
answers
425
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 ...
1
vote
1
answer
490
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 ...
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 ...
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 | ...
0
votes
1
answer
684
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, ...
-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)...