Two columns aren't unique by themselves, but must be unique together

Forum Moderators: open

Message Too Old, No Replies

Two columns aren't unique by themselves, but must be unique together

ntbgl

9:13 pm on Jun 21, 2009 (gmt 0)

I have a MySQL table that contains several columns of data.
One column, called domains is set as varchar(255), domains has values like example.com, example2.com, example3.com.

A second column, called language is set as char(2), and has values like en, es, pt.

There are instances in my database where a domain value appears multiple times, so this column is not unique, but each of these instances must have a different language value associated with them.

Allowed:
domain – language
example1.com – en
example1.com – es
example1.com – pt
example2.com – en
example3.com – pt
example4.com – pt

Not Allowed:
example1.com – en
example1.com – en
example2.com – es
example2.com – es

Is there a way to set where “this plus that” must be unique?

I add my values with insert in phpMySQL. Is there a way to prevent me from being able to insert data if a row with an equal domain and language already exists?

Thanks

FourDegreez

11:52 pm on Jun 21, 2009 (gmt 0)

create unique index name_of_index on table(field1, field2);

ntbgl

11:42 am on Jun 22, 2009 (gmt 0)

Thanks for the reply.

I'm not sure how to use this.

I assume I run this from the SQL box on phpMyAdmin, but first I need to customize it right?

create unique index [[var 1]] on table([[var 2]], [[var 3]])

Var 1 is the table name
Var 2 is a column name, like domains
Var 3 is a column name, like languages

When I try this I get:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table(`domains`, `languages`)' at line 1

coopster

1:14 pm on Jun 22, 2009 (gmt 0)

I typically perform the UNIQUE assignments when designing and creating the table. However, you can add the index later using CREATE INDEX [dev.mysql.com] which is really an ALTER TABLE [dev.mysql.com] statement.

In your case, it seems you have your syntax incorrect.

FourDegreez

3:19 pm on Jun 22, 2009 (gmt 0)

Sorry.. replace "table" with the table name.