Keep duplicates OUT OF THE DB!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Just remember that there are some limitations with using unique indexing. If you need to match case sensitive then unique indexing will not work as it is case insensitive. Password is the same as PaSSwoRd for unique indexing.

Plus if you are using stacked inserts like this one where col1 is a unique index...

"INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2), (15,col1*2), (16,col1*2), (16,col1*8)"

Then the second set of values would fail and the third and fourth would not be inserted. You could use the IGNORE command...

"INSERT IGNORE INTO tbl_name (col1,col2) VALUES(15,col1*2), (15,col1*2), (16,col1*2), (16,col1*8)"

Then the second and forth value sets would be ignored and no errors would be issued. You can't check to see what inserts failed in this case.
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

AKA Panama Jack wrote:Just remember that there are some limitations with using unique indexing. If you need to match case sensitive then unique indexing will not work as it is case insensitive. Password is the same as PaSSwoRd for unique indexing.
In this specific case, the concern was that the same username would be entered multiple times - ostensibly due to resubmits, and as a result the case would be identical. However, good point.
AKA Panama Jack wrote:Plus if you are using stacked inserts ... then the second set of values would fail
Since its a single insert (not stacked, just value-specified), it shouldn't be an issue, but another good point for people to keep in mind.
Post Reply