Use of primary key in unique indices

Consider a competition_entry table and its indices:

Code: Keyname | Type | Field ----------+---------+--------------- PRIMARY | PRIMARY | competition_id EMAIL | UNIQUE | competition_id | | email DUPLICATE | UNIQUE | competition_id | | surname | | addr_1 | | post_code The following warnings are given:
PRIMARY and INDEX keys should not both be set for column `competition_id`
More than one UNIQUE key was created for column `competition_id`

What Im trying to do is make sure that an email address is only entered once per competition and that combination of surname, first line of address and post code is also unique per competition.

My question is: why does MySQL (phpMyAdmin actually, I created the table in MySQL Administrator and it didnt say anything) say that what Im doing is bad Or, more importantly: why is it bad, if it is

Posted On: Thursday 25th of October 2012 10:40:12 PM Total Views:  490
View Complete with Replies

Related Messages:

When do I use NULL as Default value for SET, CHAR, VARCHAR data types?   (141 Views)
; I have two questions. One is about the SET data type. The other is about the CHAR and VARCHAR data types. Question 1 regarding SET I am trying to figure out when it is proper to use NULL as one of the values. For example, if I need no value do I use an empty value like this: set('', 'Yes', 'No') or would it be best to set it to NULL like this: set('NULL', 'Yes', 'No') On this page in the MySQL manual it uses NOT NULL for the SET value at the top of the page. However, about 1/8 way down the page it says that SET values are sorted numerically and that NULL values sort before non-NULL SET values. Question 2 regarding CHAR, VARCHAR Same as question above. If I need just an empty value in a table cell do I use NULL in the database table or do I just use a blank table cell. For the INT data types: I am having to use NULL for the int types because if I use an empty value PHP will display a "0" in the browser instead of no value. I would appreciate any information.
phpMyAdmin: configuring the table used to add new fields   (188 Views)
When one is adding new fields to a database table, phpMyAdmin presents a table showing all the details of the field (name, type, length/values, collation, etc.), and one types in the required information. In previous versions of phpMyAdmin these details have been displayed vertically, with the inputs on the right of each. I've now got version 3.5.1, and find that this entry table displays in a floating window with the headings horizontally and inputs below, and I have to scroll sideways to see all the fields. Very tedious, particularly when adding several fields at one time. I have spent a long time looking through the configuration files and documentation to see if I can discover how to configure this table to display vertically, but I can't see which parameter(s) to edit (if any). Can anyone help, please
Getting the index to use it later   (159 Views)
Good day, Let's I have a parent and a child tables. I load some data into the parent table, so a new record is generated with its primary key. As the private key is auto increment, I do not know the value of this index. Now, I need to load some data into the child table, where there is a foreing key linked to the parent table primary key. How can I get the value of the parent table primary key, in order to use it to load fata into the child table Parent table: "Customers" CustomerID
problem in where clause to check if the date is exist in another table   (170 Views)
Hi.. I have this code for checking if the date(TIMEOUT) is already exist in nrs table: Code: $EMP_NO = $_GET['EMP_NO']; $DATE_NRS = $_GET['DATE_NRS']; $TIME_IN = strtotime($_GET['TIME_IN']); $TIME_OUT = strtotime($_GET['TIME_OUT']); $APPROVE = $_GET['APPROVE']; $sql = "SELECT EMP_NO, TIME_IN, TIME_OUT, TOTAL_HOURS, NRS_STATUS FROM nrs WHERE EMP_NO = '$EMP_NO' AND DATE(TIME_OUT) = '$TIME_OUT'"; //echo $sql; $RsOtData = $conn2->Execute($sql); $numrows = $RsOtData->RecordCount(); if($numrows > 0){ echo "alert('Transaction cannot be process')"; echo "navigate('NRSEmp.php')"; } else{ $saverec['EMP_NO'] = $EMP_NO; //$saverec['DATE_NRS'] = $DATE_NRS; $saverec['TIME_IN'] = $TIME_IN; $saverec['TIME_OUT'] = $TIME_OUT; $saverec['TOTAL_HOURS'] = $TOTAL_HOURS; $saverec['NRS_STATUS'] = $APPROVE; $insertSQL = $conn2->GetInsertSQL($RsOtData, $saverec); $conn2->Execute($insertSQL); } now I need revise my query to check also if the date from $TIME_OUT is equal to reg_att . for example of reg_att table data: EMP_NO = 00000221 LOGIN = 2012-03-01 05:35:00 LOGOUT = 2012-03-01 13:35:00 if Date from $TIMEOUT = date(LOGOUT) if condition will work. I trid this query PHP Code: SELECTn.EMP_NO,n.TIME_IN,n.TIME_OUT,TOTAL_HOURS,NRS_STATUSFROMnrsnWHEREEMP_NO='00000221'ANDDATE(TIME_OUT)='2012-03-01'ORDATE(TIME_OUT)=(SELECTDATE(LOGOUT)FROMreg_attrWHEREr.EMP_NO=n.EMP_NO); and i got an error: Error Code : 1242 Subquery returns more than 1 row (0 ms taken) Thank you
Linking users in a table to other users in the same table.   (160 Views)
I'm trying to figure out how to design my database if I have a site where users can add other users as their favorites. So, let's say I'm logged in as John, and I got to Mary's profile. On her profile I click the "Add as favorite" button, and she is added as a favorite to my profile. How do I translate this to the database I have thought about it, and in the following image you can see what I came up with: db fav.gif Is this the way to go Or am I doing something really stupid here
how to use SAAS(plans based tables) in mysql   (244 Views)
all I am just implementing a SASS based database for a sample application. I have 3 plans and all three are different basic medium Advanced I need to develop a db tables in mysql for these 3 memberships I have different options in each plan How can I design the basic db and how can I know who is belongs to which plan using mysql Can anyone help me
How come myPHPadmin doesn't make use of deleted row numbers?   (265 Views)
I just did a practice insert that put in 1700 rows. When I verified that it worked, I deleted all of them. The next rID that was used, on the next insert, was still 1700 higher than I thought it should be. Won't mySQL make use of primary row numbers that have been deleted Otherwise I can see that primary key field getting quite high over time. Thank you. , Originally Posted by busboy Otherwise I can see that primary key field getting quite high over time. let's say you use INTEGER UNSIGNED and insert 10 rows per second any idea how long it will be before you run out of numbers spoiler: more than 10 years
outputting "word" instead of (number) with WHERE clause jewel="diamond"   (136 Views)
I have two tables like the below. Code: jewelry (id) jewel (1) diamond (2) "gold" jewels (jewel1) (jewel2) (1) (2) The result of the code1 below outputs diamondRelated (2). Code: code1 SELECT jewel2 as diamondRelated FROM jewels left join jewelry on id=jewel1 WHERE jewel="diamond" I like to output diamondRelated with the word "gold" instead of the number (2). The result of the code2 below outputs diamondRelated diamond instead of "gold". Code: code2 SELECT jewel as diamondRelated FROM jewels left join jewelry on id=jewel1 WHERE jewel="diamond" The code3 below is another trial. Since I add left join jewelry on id=jewel2 for connecting (2) to "gold", It causes an ERROR. Code: code3 SELECT jewel as diamondRelated FROM jewels left join jewelry on id=jewel1 left join jewelry on id=jewel2 WHERE jewel="diamond"
Alternative to NOT IN clause ?   (143 Views)
what causes the slow query is likely a missing index can you do an EXPLAIN on the query please
Check if user did not input details in last 3 day   (286 Views)
I have some problem. I have table with 3 fields: USER_ID, EXERCISE_MINUTES and PROGRESS_DATE for example we have rows like this: |USER_ID|EXERCISE_MINUTES|PROGRESS_DATE| 3 10 2010-09-05 9 0 2010-09-11 9 8 2010-09-12 9 20 2010-09-13 2 10 2010-09-10 2 15 2010-09-11 2 12 2010-09-12 2 12 2010-09-13 5 10 2010-09-09 5 0 2010-09-11 5 0 2010-09-12 5 0 2010-09-13 From this rows I should select USER_ID where USER have EXERCISE_MINUTES = 0 in last three days or where user did not enter exercise for more than 3 days. I.e. I should get users with USER_ID: 3 and 5... how to create query like this
Will converting a column from enum to Varchar(20) cause data to be lost?   (262 Views)
, We have a column that is currently enum, containing a selection of possible choices that are at maximum 15 Chars. We would like to change this column to be a Varchar(20), would this Alter keep the data in the current enum safe, that is convert them to be just Char data under the new Varchar(20) or will conversion from enum to Varchar cause the loss of data If yes, that is conversion from enum to varchat is not possible, what to you suggest as the best method to getting this objective realized
update in FROM clause   (233 Views)
I need to update isdone flag to 1 for ccallid is same like primaryoldcall or secondaryoldcall update ev_callcontrol set isdone = 1 where ccallid in (select primaryoldcall from ev_callcontrol where ccallid=18) or ccallid in (select secondaryoldcall from ev_callcontrol where ccallid=18) but I getting: Error Code : 1093 You can't specify target table 'ev_callcontrol' for update in FROM clause (16 ms taken) could any body please help
subquery max group needs and additional where clause   (293 Views)
I have a discussion forum that I would like to filter approved posts. I would like to know how to select the max(datetime) but only if the max has an approved value of 1. The only way I can think to do this is adding a where clause of approved = 1 but this will not work with a group by. Any suggestions Code MySQL: select * from questions where datetime in (select max(datetime) from questions group by id )
[MySQL] UPDATE Clause   (135 Views)
Code: UPDATE tbl SET score = 5600 WHERE name = 'Ronald' ORDER BY score ASC LIMIT 1 Hopefully you only have one ronald in the table.
1000 queries vs where userid='1' or userid='2',...   (249 Views)
Each user have a list of friends from Facebook. I need to check if any of them is already a member of our site. I get a list of FB friends in array. Is there any better way than doing the following PHP Code: foreach($friendsas$key=>$value){ $results=mysql_query("SELECT*FROM`users`WHEREuserId='$value'")ordie(mysql_error());} or another way: PHP Code: $where="userId='435345434'";foreach($friendsas$key=>$value){ $where=$where."oruserId='".$value."'";}$results=mysql_query("SELECT*FROM`users`WHERE$where")ordie(mysql_error()); I hope anybody can help with this. tnx!
#1045 - Access denied for user 'root'@'localhost' (using password: NO)   (167 Views)
When getting this error after trying to login to your local mysql. Do the following: Open your mysql program folder. Find and click on : resetroot.bat Close xammp and restart. Let me know if this helps
seeking csv file or similar of user agents.   (171 Views)
Been googling for ages now and seem to draw a blank. Has anyone got a link to where I can download a csv file, or similar, of all useragents and browser names Paid for or free, so long as they are accurate. bazz
Several columns in the WHERE clause   (154 Views)
In the following query I would like to search for more than `name`, for example: WHERE `name` `brand` `size` LIKE '%q%' but, I don't know the correct syntax to follow. Code MySQL: SELECT * FROM ( SELECT * FROM `products` WHERE `name` LIKE '%q%' AND `user_id` = '$user_id' ) AS results WHERE `status` = 0 OR `status` = 1 OR `status` = 2 OR `status` = 3 I need help
Programs you use to design a new database?   (223 Views)
Recently I've started working one a new site and this will be my first site that uses a database that wasn't preconfigured (wordpress / drupal). I'm just curious what uses to design their database structure, layout the types/primary keys etc... I've been doing it in Microsoft Excel. I gave Microsoft Access a try but found it tedious when everything is changing around so much. When it comes to laying out the relationships I may have to try it again but until then Excel seems to be working. Eventually when done the design I will be using MySQL Database. So, do you use Excel, Access, Pencil/Paper, handbomb trial/error, etc... ,
Can Crystal Reports be used with MySQL?   (175 Views)
Can Crystal Reports be used with MySQL If not what reporting tool do you recommend