Multiple Column Duplicate Entry Check MySQL

Question

I'm aware that you can create a unique column in your MySQL table, but I'm actually looking to compare TWO columns.

So if I had records like:

Time          User       Table
10:00pm       Fred       29
11:00am       Bob        33

I COULD insert a new record with the time 10:00pm and table 33 but not 10:00pm table 29.

I know I could run a query and then nullify my ability to insert a new record if I had results from that query based on comparing those two fields, but I'm wondering if there is a more elegant solution wherein I can get a duplicate entry error from MySQL on the INSERT and save myself a few lines of code.

I wish I knew those words 2 hours ago. I googled that and came to this exact conclusion on this site. At least people like me who haven't figured it out will have a matching query now. — Howard Zoopaloopa
– Howard Zoopaloopa, Commented Jan 21, 2012 at 0:03

tadman · Accepted Answer · 2012-01-20 19:52:56Z

3

You can create a unique index that incorporates both columns:

CREATE UNIQUE INDEX idx_time_and_table ON reservations (`Time`, `Table`);

This will block any inserts for the same pairing provided both values are not NULL. Having a NULL value side-steps the unique checking.

You're also using reserved SQL keywords for your column names which you might want to change.

answered Jan 20, 2012 at 19:52

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Howard Zoopaloopa Over a year ago

my sql keywords were changed in this question to protect the innocent.

Mike Purcell · Accepted Answer · 2012-01-20 19:52:52Z

3

Try using a composite unique constraint across both columns:

ALTER TABLE your_table ADD UNIQUE(`Time`, `Table`);

Now any rows attempting to be added that have matching values will force MySQL to throw an error, which you can test for in your app.

answered Jan 20, 2012 at 19:52

Mike Purcell

20k10 gold badges54 silver badges94 bronze badges

Comments

slaakso · Accepted Answer · 2012-01-20 19:54:01Z

2

Create an unique index based on the columns you want to be unique:

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

answered Jan 20, 2012 at 19:54

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

Multiple Column Duplicate Entry Check MySQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related