create unique index

junemo · July 23rd, 2004, 03:12 AM

Hi, can someone help me how to create a UNIQUE constraint on the table to allow MySQL to reject the duplicate records?

Can MySQL database to create unique index?

I want it can ensure that the combination of product ID and serial number is unique. I can have multiple rows with the same product ID, and multiple rows with the same serial number, but not multiple rows with the same product ID and serial number.

I using MySQL Control Center to create a table. So, have anyone can help me how to do?

Please...

Thanks in advance...

happygv · July 24th, 2004, 04:24 PM

Hi there,
You can use this syntax for doing so.

Code:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type]
    ON tbl_name (index_col_name,...)

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

junemo · July 25th, 2004, 07:23 PM

Hi,
if using MySQL Control Center then how to create it?
if not, where should I run this script?

I have a table:
Table name: prod_batch
I want this 2 field to create as unique index.
field: prod_id , s1.

Then, what should I fill in with this script?

Quote:

quote:

Code:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type]
    ON tbl_name (index_col_name,...)

Thanks in advance.

happygv · July 26th, 2004, 01:28 PM

This should be enough.

Code:

CREATE UNIQUE INDEX MyUniqueIndex
    ON prod_batch (prod_id , s1)

You can run this from your mysql prompt.

Or from Mysql Control center (I forgot the exact location where the options are found.but still you can...) there is an option to run sql statements, you can copy/paste it there and execute it to create one.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

junemo · July 26th, 2004, 07:08 PM

HI,

unique index created.

Thanks in advance..........

crapanz · September 20th, 2004, 11:34 PM

You know what I'm wondering about:
If you have a unique index on both fields, and you say that
one field can have a value and other left blank NULL, and
vice-versa, wouldn't you violate the index if you have multiple rows with the same product ID, because the serial number will be NULL.?

:D

happygv · September 22nd, 2004, 05:11 PM

Quote:

quote:I can have multiple rows with the same product ID, and multiple rows with the same serial number, but not multiple rows with the same product ID and serial number.

Did you read that from original post?

Unique index means it lets the user have every row as unique to the other. So it lets you have
1) Same productId but different serial number
2) Different ProductId with same serial number.
3) one NULL value for productID/Serial number. Stil this can be restricted to NOT NULL at the column level.

I don't see this setup violates the index or the original post saying serial numbers will be NULL...

Hope that explains.
Cheers!

_________________________
- Vijay G
Strive for Perfection