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. ...
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...
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...
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...
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...
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 ...
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...
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...
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...
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 ...
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...
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 ...
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...
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...
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...
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...
What is the diffrence between a unique index and a unique key? ...
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 ...
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)...
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...