Stack Overflow
Stack Overflow

Questions tagged [unique-index]

Unique-index is an index that sets one or multiple fields to be unique in a data entity

A unique index on a table guarantees that the index key contains no duplicate values and therefore ensures that every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a essential for the data in a table.

A unique index on a field (or set of fields) prevents two records from existing in the table with the same value in the indexed field(s). ServiceNow uses several unique indexes out-of-box to protect certain tables from duplicate record confusion.

333 questions
63
votes
5 answers

Unable to create index because of duplicate that doesn't exist?

I'm getting an error running the following Transact-SQL command: CREATE UNIQUE NONCLUSTERED INDEX IX_TopicShortName ON DimMeasureTopic(TopicShortName) The error is: Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement …
Alex Angas
  • 59,219
  • 41
  • 137
  • 210
59
votes
16 answers

Short unique id in php

I want to create a unique id but uniqid() is giving something like '492607b0ee414'. What i would like is something similar to what tinyurl gives: '64k8ra'. The shorter, the better. The only requirements are that it should not have an obvious order…
Antti
  • 3,119
  • 3
  • 24
  • 22
53
votes
7 answers

Unique index or unique key?

What is the diffrence between a unique index and a unique key?
fariba
  • 665
  • 1
  • 8
  • 7
47
votes
9 answers

How do I delete all the duplicate records in a MySQL table without temp tables

I've seen a number of variations on this but nothing quite matches what I'm trying to accomplish. I have a table, TableA, which contain the answers given by users to configurable questionnaires. The columns are member_id, quiz_num, question_num,…
MivaScott
  • 1,763
  • 1
  • 12
  • 30
46
votes
4 answers

Unique Constraint vs Unique Index

I’m interested in learning which technique developers prefer to use to enforce uniqueness in SQL Server: UNIQUE CONSTRAINT or UNIQUE INDEX. Given that there is little difference in the physical implementation of each, how do you decide which is…
bobs
  • 21,844
  • 12
  • 67
  • 78
32
votes
4 answers

Removing duplicates with unique index

I inserted between two tables fields A,B,C,D, believing I had created a Unique Index on A,B,C,D to prevent duplicates. However I somehow simply made a normal index on those. So duplicates got inserted. It is 20 million record table. If I change my…
user3649739
  • 1,829
  • 2
  • 18
  • 28
31
votes
1 answer

how to set a column as unique indexer on Sqlite

I have 3 columns (_id, column1, column2) _id column has been set as autoincrement In database there are some duplicate records, so I want to prevent duplicate records with setting column1 as unique indexer. How do I set a column as unique indexer on…
Mustafa Güven
  • 15,526
  • 11
  • 63
  • 83
28
votes
1 answer

Should I specify both INDEX and UNIQUE INDEX?

On one of my PostgreSQL tables, I have a set of two fields that will be defined as being unique in the table, but will also both be used together when selecting data. Given this, do I only need to define a UNIQUE INDEX, or should I specify an INDEX…
Matt Huggins
  • 81,398
  • 36
  • 149
  • 218
23
votes
1 answer

MySQL Make a combination of columns unique

I have a table that stores comments users make about images on the site. The table is structured with four columns, the row_id, which is the primary key, the image_id, the user_id and the comment. What I want to do is ensure that a user can only…
Wige
  • 3,788
  • 8
  • 37
  • 58
20
votes
1 answer

Add unique index. SQLite3

I need to add a unique field index to an existing table. I made this row: ALTER TABLE auth_user ADD UNIQUE INDEX (email); The table and the field are already exist. The error is: Query Error: near "UNIQUE": syntax error Unable to execute…
I159
  • 29,741
  • 31
  • 97
  • 132
16
votes
2 answers

Rails uniqueness constraint and matching db unique index for null column

I have the following in my migration file def self.up create_table :payment_agreements do |t| t.boolean :automatic, :default => true, :null => false t.string :payment_trigger_on_order t.references :supplier …
Dave
  • 161
  • 1
  • 1
  • 3
16
votes
2 answers

What is the purpose of non unique indexes in a database?

I've searched around but I can't seem to find an appropriate explaination behind the concept of unique and non unique indexes in a database. In Rails, for example, you can create unique and non unique indexes for a given field, as explained at…
Redoman
  • 3,059
  • 3
  • 34
  • 62
15
votes
1 answer

Behavior of unique index, varchar column and (blank) spaces

I'm using Microsoft SQL Server 2008 R2 (with latest service pack/patches) and the database collation is SQL_Latin1_General_CP1_CI_AS. The following code: SET ANSI_PADDING ON; GO CREATE TABLE Test ( Code VARCHAR(16) NULL ); CREATE UNIQUE INDEX…
Eric
  • 3,143
  • 2
  • 16
  • 16
15
votes
1 answer

Add constraint to make column unique per group of rows

There is a column status in a Postgres table which can take only two values: Active and Inactive. One of the columns is named userid. The table can have multiple rows with the same userid but at most one of them can have status = 'Active'. I need…
Shashwat Kumar
  • 5,159
  • 2
  • 30
  • 66
15
votes
2 answers

Advantage of a unique index in MongoDB

I've tried to search through Mongo documentation, but can't really find any details on whether queries on unique indexes will be faster than queries on non-unique indexes (given the same data) So I understand that a unique index will have high…
Zaid Masud
  • 13,225
  • 9
  • 67
  • 88
1
2 3
…
22 23