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
14
votes
1 answer

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

I've been trying to sort out the relationship between unique and index in Postgres after reading the docs on index uniqueness being an implementation detail: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT.…
Finn
  • 1,823
  • 2
  • 15
  • 31
14
votes
3 answers

Check whether a field has the property `UNIQUE` in mysql and PHP

How can I check whether a field from a table is set as UNIQUE? For example I have a table named users with a field email set as UNIQUE and a field picture not set as UNIQUE, I want before selecting check whether the field is set set as UNIQUE if not…
Jonathan de M.
  • 9,721
  • 8
  • 47
  • 72
12
votes
1 answer

SQL Server Unique Index across tables

It's possible to create a unique index across tables, basically using a view and a unique index. I have a problem though. Given two (or three) tables. Company - Id - Name Brand - Id - CompanyId - Name - Code Product - Id - BrandId - Name -…
Phill
  • 18,398
  • 7
  • 62
  • 102
10
votes
3 answers

How to Create Unique Index for Existing table in MySQL which contains Records

Here i like to explain my problem, I Need to Create Unique Index in my Existing table, and the table contains many records. I tried to execute this code CREATE UNIQUE INDEX empid_name ON employee (importcompany_id, employee_id, name,…
Nodemon
  • 1,036
  • 2
  • 25
  • 47
9
votes
3 answers

Is there any way to make a UNIQUE index case insensitive in Mysql 5.1.x ?

If so - What must change in this table ? CREATE TABLE contestants ( idContestants int(10) unsigned NOT NULL AUTO_INCREMENT, idEvent int(10) unsigned NOT NULL, ContestantName varchar(50) DEFAULT NULL, PRIMARY KEY (idContestants), …
Charles Faiga
  • 11,665
  • 25
  • 102
  • 139
9
votes
1 answer

PostgreSQL: unique constraint or unique index

Should I create unique index if a column contains unique constraint and I want to fast search by this column? For example I have a table users with column login that should be unique. I need fast search user by the login column. Which is the best…
Alexander
  • 409
  • 4
  • 11
9
votes
2 answers

Can not drop UNIQUE index from table

When I run this query ALTER TABLE "dbo"."ROOM" DROP INDEX "UNIQUE"; I got this message: Error 1018: Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books…
Perimosh
  • 2,304
  • 3
  • 20
  • 38
9
votes
1 answer

How to partition MySQL table by column that is not in the unique index

Let's have a simple table of products. Each produch has its unique ID and category. Users often search by category so I want to partition products by category. Each category in one partition e.g. How do I do it? Because of course I have a primary…
Josef Sábl
  • 7,538
  • 9
  • 54
  • 66
8
votes
4 answers

How can I constrain multiple columns to prevent duplicates, but ignore null values?

Here's a little experiment I ran in an Oracle database (10g). Aside from (Oracle's) implementation convenience, I can't figure out why some insertions are accepted and others rejected. create table sandbox(a number(10,0), b number(10,0)); create…
Chris
  • 632
  • 4
  • 11
  • 18
8
votes
3 answers

SQL Server - Unique index vs Unique constraint - Re. Duplicate values

A unique index ensures that the values in the index key columns are unique. A unique constraint guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a…
variable
  • 8,262
  • 9
  • 95
  • 215
7
votes
3 answers

In MYSQL, what does it mean when there are duplicate indices where everything but key_name is the same?

describe etc_category_metadata; +---------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra …
Popcorn
  • 5,188
  • 12
  • 54
  • 87
7
votes
3 answers

How wrong is it to have a unique and normal index on the same column?

I have the following table structure CREATE TABLE `table` ( `id` int(11) NOT NULL auto_increment, `date_expired` datetime NOT NULL, `user_id` int(11) NOT NULL, `foreign_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `date_expired`…
Darryl Hein
  • 142,451
  • 95
  • 218
  • 261
7
votes
6 answers

cakephp isUnique for 2 fields?

I have a registration form in which users can fill in two email address (email1 & email2). Marketing's requirement is that they need to be unique (unique as in if we had 10 users, then there would be 10*2=20 unique email address). The system is…
jodeci
  • 966
  • 2
  • 11
  • 18
7
votes
2 answers

email as _id in a MongoDB user collection

I have a user collection in a MongoDB. The _id is currently the standard MongoDB generated ObjectId. I also have a unique key constraint against a required 'email' field. This seems like a waste. Is there any reason why I should not ditch the…
Guy
  • 65,082
  • 97
  • 254
  • 325
7
votes
4 answers

Enforcement of unique/primary key - drop index

I am trying to drop an index : DROP INDEX PK_CHARGES but I get this error cannot drop index used for enforcement of unique/primary key Why I am getting this error? I will provide further information if you need any. How to solve it? Edit I have…
Moudiz
  • 7,211
  • 22
  • 78
  • 156
1
2
3
…
22 23