UNIQUE property across multipile fields

[gjlowe]

Ok, I have a question that I may not explain quite right...

I have a MYSQL table that I am inserting to using a php script. There are two fields that may contain duplicate entries themselves, but I want to make sure that NO record in the table has both fields with duplicate entries at the same time. Here is an illustration to try to explain:

Field A I will call firstname
Field B I will call birthdate

Now, records 1, 2, & 3 are valid, but I want Record 4 to reject because the field A & Field B can not be duplicated across the table at the same time.

Record 1:
firstname can be "Bob"
birthdate can be "1977"

Record 2:
firstname can be "Bob"
birthdate can be "1976"

Record 3:
firstname can be "Larry"
birthdate can be "1977"

Record 4:
firstname = "Bob"
birthdate = "1977"

Am I just creating the table incorrectly, or is there a way to make these two fields together a unique key?

[eoghain]

Create a unique index that uses both of those columns.

Something like this:

create unique index my_idx on my_table(firstname, birthdate);

[gjlowe]

What is the "my_idx" field you have listed?

[eoghain]

my_idx isn't a field it's a name. It's whatever you want to call your index. You should double check the Create Index syntax for your database. What I posted may not be correctly formatted for the DB that you are using.

[gjlowe]

I will try it. I am using MySQL, and unfortunately, have only had time to learn as I go!