|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
January 16th, 2003, 12:19 AM
|
|||
|
|||
|
two-field keys in MySQL?
Quick question. Is it possible in MySQL to specify a key for a table that depends on two fields?
For example, I have the following fields: Name, Address, Phone Can I make the db not allow new rows ONLY if name AND address are the same as those in another previous row? If only Name OR Address (but not both) are the same as another row, then there is no key violation and it gets added. Thank you very much in advance.
|
|
#2
January 18th, 2003, 02:07 PM
|
|||
|
|||
|
Joe,
You can make those 2 fields UNIQUE... This will ensure that no duplicates are added.
__________________
____________________________________________ Developer Shed Weekly Writer | DevArticles Forum Moderator Build Your Own KlipFolio Klip With PHP FrankManno.com - Under Construction Design Interactive Group - Under Construction
|
|
#3
January 18th, 2003, 07:41 PM
|
|||
|
|||
|
Thanks for the reply.
But I think that if I made both unique I would still have the problem... Basically, i want to allow two names to be the same in different rows, or two addresses to be the same in different rows. BUT I dont want two rows with the same name AND address. Do you know any way of getting this? Thanks Joe
|
|
#5
January 19th, 2003, 07:47 PM
|
|||
|
|||
|
Thanks for the help. Thats what I thought ...
The reason I wanted to do it this way is because this is a db with about 30k records. And it seems like it would be very inefficient to query ALL the records to see if this is happening every time a new record is added. Any comments on this?
|
|
#6
January 19th, 2003, 07:52 PM
|
|||
|
|||
|
What do you think mySQL would do, if it was built in?
The only way would be search through all the records looking for a match. However, this shouldn't be very time consuming provided that you have indexed both fields. Hadley
|
|
#7
January 22nd, 2003, 07:05 PM
|
|||
|
|||
|
If I understand you correctly, it is possible:
Code:
create table phonenumbers ( name varchar(64) not null, address varchar(128) not null, phone char(10), primary key (name, address) ); Is that what you're looking for? Cheers, Andrew Last edited by avit : January 23rd, 2003 at 08:38 PM.
|
|
#8
September 8th, 2004, 03:47 AM
|
|||
|
|||
thank you for your reply!
Hi Andrew,
although I didn't start the thread, I really appreciate your example mysql statement - I was happy to find an answer to my question that quickly! Too bad that some posters always forget to write a simple "Thank you" after others spent their time to find answers to the posted problem(s) ...  Have a great day!  Sascha
|
|
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > two-field keys in MySQL? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
