unique-index

should nearly unique fields have indexes

I have a field in a database that is nearly unique: 98% of the time the values will be unique, but it may have a few duplicates. I won't be doing many searches on this field; say twice a month. The table currently has ~5000 records and will gain about 150 per month. Should this field have an index? I am using MySQL. ...

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` (`date_expired`,`user_id`,`foreign_id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEF...

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 and that it should look prettier than a seemingly random sequence of numbers. Letters are p...

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 unique index sandbox_idx on sandbox(a,b); insert into sandbox values (1,1); -- accepted ins...

MySQL Views in Navicat - How to define 'primary key'?

Often when I define a View in Navicat I receive the following message: xxx does not have a primary key. Updates to this table will be done using the following pseudo statement: UPDATE xxx SET ModifiedFieldsAndValues WHERE AllFieldsAndOldValues LIMIT 1 Obviously I only use my Views for viewing data, not updating. But this did make m...

SQL Server unique index allowing duplicates

I am using SQL Server 2008, had a table with an id (numeric) column as the primary key. Also had a unique index on three varchar columns. I was able to add a row with the exact same set of the three columns. I verified it with a simple query on the values and 2 rows were returned. I edited the index and added the id column. When I ...

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 terminated because a duplicate key was found for the object name 'dbo.DimMeasureTopic' an...

Check if Database raises a particular exception

using Nhibernate; I'm trying to insert several values a on table which has an unique index on some columns. I'd like to know if a particular insert raises an exception for having violated the unique constraint. So, which particular exception type should i catch? I only want to catch this particular one and let all others go up. Thank...

Problem with updating multiple rows which are in conflict with unique index

I am using Microsoft SQL Server and I have a master-detail scenario where I need to store the order of details. So in the Detail table I have ID, MasterID, Position and some other columns. There is also a unique index on MasterID and Position. It works OK except one case: when I have some existing details and I change their order. For ex...

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 t.references :seller t.references :product t.timestamps end end I want to ...

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 best? Are there reasons other than performance to evaluate the best solution? Are there da...

how to use UNIQUE index correctly?

i have 4 fields at DB.i set them become cant duplicate entry.They are: 1. Model Varchar(14) Unique 2. Serial varchar(8) Unique 3. Lot varchar(5) Unique 4. Line char(5) Unique Model Serial Lot Line First data remocon x0001 ...

SQL Server 2008: Collation ignored in unique index?

I've created a compound unique index on my table: CREATE TABLE [dbo].[SearchIndexWord]( [ID] [int] IDENTITY(1,1) NOT NULL, [CatalogID] [int] NOT NULL, [Word] [nvarchar](100) NOT NULL, CONSTRAINT [PK_SearchIndexWord] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY...

Hibernate throws unique constraint violation exception while updating field part of unique key

Hi All, Below is the use case: I have a unique index defined on 3 columns say A,B,C. Assume the values in them are A1,B1,C1. My java code is adding a new record say A1,B1,C1 but before this record is added, i update the previous value from C1 to C2. While trying to add the new record (after the update), hibernate is throwing an unique c...

Which approach is better for this scenario?

Hello SQL Gurus. We have the following table: CREATE TABLE [dbo].[CampaignCustomer]( [ID] [int] IDENTITY(1,1) NOT NULL, [CampaignID] [int] NOT NULL, [CustomerID] [int] NULL, [CouponCode] [nvarchar](20) NOT NULL, [CreatedDate] [datetime] NOT NULL, [ModifiedDate] [datetime] NULL, [Active] [bit] NOT NULL, CONST...

ActiveRecord association: create new association or reference existing if associated attributes match

I have two models below. They can be explained as follows: A report has a report_detail (which determines start/end months). Many reports can have the same report detail, but no two report details can be the same. class Report < ActiveRecord::Base # attr: name :: String # attr: report_detail_id :: Integer belongs_to :report_de...

Unique index or unique key?

What is the diffrence between a unique index and a unique key? ...

UNIQUE argument for INDEX creation - what's for?

Why does INDEX creation statement have UNIQUE argument? As I understand, the non-clustered index contains a bookmark, a pointer to a row, which should be unique to distinguish even non-unique rows, so insuring non-clustered index to be unique ? Correct? So, do I understand that no-unique index can be only on clustered table? since ...

Mysql add mult-column index with repeated rows.

Having the next table: id -> incremental field_1 -> foreignkey field_2 -> foreignkey I want to add the next index ALTER TABLE my_table ADD unique index(field_1, field_2); How ever I have (due a bad application validation) I have a lot of repeated rows (by repeated I mean same field_1 and same field_2, having just id as difference)...

How do I set up an index on a pair of columns so that the values are tied together?

I have a table with id, external_id and country_code columns. I have two rules which I want the database to impose: Each External ID can only appear once per country code Each ID can only appear with at most one non-null External ID, and vice versa. The first rule is easy enough - I add a unique multi-column index to external_id and...