A set of zero, one or more attributes, the value(s) of which are guaranteed to be unique for each tuple (row) in a relation.

learn more… | top users | synonyms

49
votes
5answers
15k 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 ...
27
votes
3answers
19k 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), ...
17
votes
1answer
21k views

Optimal way to ignore duplicate inserts?

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 ...
8
votes
4answers
445 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 ...
8
votes
3answers
1k 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 ...
8
votes
3answers
4k 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 ...
7
votes
3answers
5k views

Differences between “Unique Key” and “Primary Key”

What are the major differences between Unique Key and Primary Key in MySQL?
7
votes
2answers
8k 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, ...
6
votes
1answer
1k views

UNIQUE index key violation

I have a table with a PK and a unique non-clustered index, as follows: CREATE TABLE Table1 ( Id INT IDENTITY(1,1) NOT NULL, Field1 VARCHAR(25) NOT NULL, Field2 VARCHAR(25) NULL, ...
5
votes
2answers
736 views

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

Is it possible to have a custom unique column 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 ...
5
votes
2answers
182 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
1answer
399 views

Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE index on all fields except for the PRIMARY KEY. The SHOW CREATE TABLE: ...
5
votes
1answer
217 views

Unexpected Unique Constraint Violation

Given the following CREATE TABLE statement: CREATE TABLE [dbo].[Unit] ( [id] SMALLINT NOT NULL IDENTITY(1,1), [name] VARCHAR(100) NOT NULL, CONSTRAINT ...
5
votes
2answers
738 views

Resolving DUPLICATE for a column with UNIQUE

I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test ...
4
votes
1answer
2k 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 ...
4
votes
2answers
2k views

MySQL: Storing unique URLs

I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
4
votes
1answer
107 views

Should I always make an attribute id that's an primary key?

I was thinking, sometimes several columns in a table uniquely specify the whole row. In that case, is there a need to create a special column for a primary key? Is that good for something?
4
votes
2answers
331 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, ...
4
votes
2answers
472 views

What happens to the index of a primary key after a DROP CONSTRAINT?

I am running PostgreSQL 9.1.4. I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to : 1 - Remove the pkey constraint on the ...
3
votes
1answer
209 views

Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Is there any different how mysql treats UNIQUE (column1, column2), INDEX (column1) and UNIQUE INDEX (column1, column2) and UNIQUE (column1, column2) In the latter, I think mysql will ...
3
votes
2answers
643 views

checksum(newid()) as primary key - what happens if a collision occurs?

Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). ...
3
votes
3answers
1k 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: ...
3
votes
1answer
857 views

Duplicate UNIQUE KEY error after ROLLBACK in MySQL

I have a table like this mysql> describe seudonimos; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
2
votes
4answers
2k 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 ...
2
votes
1answer
146 views

Does a SQL server unique constraint partially or fully fail a multi-row write?

If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?
2
votes
1answer
518 views

SQL Uniqueness Across Multiple Tables

I have two SQL tables: Users and clients. Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field. The users ...
2
votes
1answer
240 views

Foreign Key NULL vs Foreign Key to an empty string

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) CREATE TABLE medicine ( id serial PRIMARY KEY, ...
2
votes
1answer
6k 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
2answers
2k views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
2
votes
2answers
54 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. ...
2
votes
2answers
51 views

Strategy to find the unique bivariate polynomials

I have table that stores Tutte polynomials of a large set of graphs. These are bivariate polynomials with integer coefficients. An example of a polynomial taken from Wikipedia is: Since the ...
2
votes
1answer
146 views

ER relation with unique key

I've a problem about my ER diagram. Assume that I want to draw a diagram with student, member and team. Here is my specs: A student does not have to be a member of any team. A student could be a ...
2
votes
2answers
174 views

MySQL - unique constraints design

I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community. Please consider the diagram: I would like to add a unique ...
2
votes
1answer
267 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 ...
2
votes
1answer
493 views

MySQL Unique Index Keylength

I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key. The engine of choice here is InnoDB. There are ...
2
votes
1answer
1k views

Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

I dropped my UNIQUE KEY index 'myindex' on a table and I wanted to change the UNIQUE KEY to something else, so after dropping it I did: alter table mytable add unique key anykey (add1, city, state); ...
2
votes
0answers
56 views

Unique Index much bigger than the table itself

Pg 9.3 on debian 7. I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
1
vote
1answer
2k views

Duplicate key violates unique constraint

When I use this query everything is fine: UPDATE users SET f_name='Mike', l_name='MyLastName', username='blabla', "password"='asdfsdaf', "class"=12, ...
1
vote
2answers
323 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
1answer
119 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 ...
1
vote
1answer
178 views

Unique constraint on multiple nullable columns Sql Server

We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null). We are using this table to figure out which price to assign various combos ...
1
vote
2answers
94 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 ...
1
vote
1answer
108 views

MySql - unique key not working

I have the following table: CREATE TABLE `timeline_lists` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `list_type` int(11) NOT NULL, `s_object_id` int(11) NOT NULL ...
1
vote
2answers
422 views

Unique Constraint with multiple null columns

I have two tables PC(Id,EmpName Not NULL, PCName NULL, HostName NULL,.... PhysicalLocation NULL, PCType Not NULL) PCNetwork(Id, EmpName Not NULL, PCName NULL, HostName ...
1
vote
1answer
28 views

Duplicate Data from two column

i have a table which has only two column one is pid(Photo ID) and another one is tid(Tag ID) +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
57 views

What problem prevents well defined behavior of a foreign key on a non-unique column?

In my noob opinion, it strikes me that in a serialized environment, this shouldn't be a problem. Is that correct? If not, please explain to me why a foreign key's behavior on a non-unique column is ...
1
vote
1answer
113 views

Merge Problems SQL Server

I'm trying to create a procedure that will take the values from a temporary table, and load them into another table. The second table has a composite unique constraint across its three columns (Not ...
1
vote
2answers
116 views

Relationship with a table without unique/primary key

I have a database with 3 tables. Table Work, with a field named workName. Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName. And table Activity with ...
1
vote
1answer
52 views

Is it better to add an index and search via the index or add a unique constraint and let an insert fail?

I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job: mysql> DESCRIBE job_views; ...
1
vote
1answer
61 views

Correct data model for users with clients

I have an application where users are granted access. Part of the system allows users to have their own 'clients'. I have a users table ******************************** users ...