Page 1 of 1
To insert unique records, do I have to do something in PHP
Posted: Tue Dec 18, 2007 5:06 pm
by legend986
I have a table with four columns... A B C D
I want the B,D combination to be unique while inserting the records. For example, if there was a record already, then I don't want to insert that. Is this a database issue or do I have to do some SELECT in php to see if I get any records and then insert?
Also, how would I use the distinct for multiple columns? At present, if I say SELECT DISTINCT(A), it gives me all records not repeating in column A but I want to do this on multiple columns... Is there a way for this?
Posted: Tue Dec 18, 2007 6:36 pm
by feyd
Make a unique key that's B,D.
Posted: Tue Dec 18, 2007 10:03 pm
by legend986
You mean I should add the unique attribute to B and D? Like this perhaps?
Code: Select all
ALTER TABLE table_name ADD UNIQUE (B,D);
But I thought it'll add B and D as unique and has nothing to do with maintaining unique combination of B and D... Can you advice me further please?
Posted: Tue Dec 18, 2007 10:20 pm
by aliasxneo
Maybe I'm not thinking straight but even if it did add them separately it wouldn't matter.
Assume B is 1 and D is 2. If you insert that into the db that exact combination can never happen again because B can never be 1 and D can never be 2. Now D can be 1 and B 2, but that's not the same combination.
Posted: Tue Dec 18, 2007 10:22 pm
by Kieran Huggins
Careful here - if either B or D are NULL then MySQL won't force uniqueness at all.
Posted: Tue Dec 18, 2007 10:34 pm
by legend986
Yes... I will take care of the NULL issue... The problem with the approach is something like this: Consider this case:
B,D
1,2
1,3
1,4
2,4
In this case, though B,D are unique, B is not unique... I want a unique combination not unique columns... That is the problem I am currently facing...
Posted: Tue Dec 18, 2007 10:45 pm
by Kieran Huggins
You need a unique KEY - not a unique column. The syntax is:
Code: Select all
CREATE TABLE .... UNIQUE KEY `keyname` (`B`,`D`)
Does that work?
Posted: Tue Dec 18, 2007 11:34 pm
by legend986
Thank you so much... That did the trick... Never thought that there would be a unique column and a unique key though it makes proper sense now as to why they have this...