List:General Discussion« Previous MessageNext Message »
From:mfatene Date:June 28 2005 2:06pm
Subject:RE: create unique index
View as plain text  
>> From: Scott PurcellDate: June 28 2005 3:36pm
>> Subject: create unique index
>>
>> Hello,
>> I am reading the docs, but I am slightly confused.
>>
>> I have a table with a varchar(50) column (not a primary column) where I =
>> do not want duplicates. It is a properties column, and I am getting =
>> duplicates inserted, which is causing problems in my display.
>>
>> An Oracle DBA that works with me suggested creating a unique index on =
>> the column. I am reading the docs here:
>> http://dev.mysql.com/doc/mysql/en/create-index.html
>> but I am not have a clear understanding of an index, so I am having =
>> trouble visualizing what I need to do. The column already exists.=20
>>
>> I am running 4.0.15 on a PC. The current column type is: MyISAM. I am =
>> not sure if that is proper or not. Its usage is for a web-site.
>>
>> Here is what I created a while back:
>> CREATE TABLE PROPERTIES (
>>        property varchar(50),
>>        value varchar(200),
>>        description varchar(200)
>> ) TYPE=3DMyISAM;
>>
>> Also, if this is doable, can I also create an index across two columns? =
>> I have another situation where I need a combination of two columns to be =
>> unique.
>>
>> Thanks,
>> Scott
***********************************


Hi scott,
I think that what you want to do is this :
mysql> CREATE TABLE PROPERTIES (
    ->        property varchar(50),
    ->        value varchar(200),
    ->        description varchar(200)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> create unique index UNQ on PROPERTIES(property,value);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into properties values('test1','Val of test1','test');
Query OK, 1 row affected (0.02 sec)

mysql> insert into properties values('test1','Val of test1','test');
ERROR 1062 (23000): Duplicate entry 'test1-Val of test1' for key 1
mysql>

Mathias
Thread
create unique indexScott Purcell28 Jun
  • Re: create unique indexAlec.Cawley28 Jun
RE: create unique indexmfatene28 Jun