How to create a candidate / super key using MySQL 5.1. I tried in internet, no use. Please help me out... Its like this. I have tags table. The structure is given below. Code: CREATE TABLE `tags` ( `tid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `cid` INT NOT NULL , `name` VARCHAR( 250 ) NOT NULL ) ENGINE = InnoDB; I need it to be like this. `tid` is obviously uncontrollable. But no entry should have something like this. Code: TID CID Name 1 1 Wordpress 2 1 Facebook 3 1 Facebook - [COLOR=Sienna]This should not come![/COLOR] I used this code in PHP, but is not working: Code: if(mysql_num_rows("SELECT * FROM `tags` WHERE `cid` = ".$lcid." AND `name` = '".$ltag."';") == 0) if(mysql_query("INSERT INTO `tags` ( `tid` , `cid` , `name` ) VALUES ( NULL , '".$lcid."', '".trim($ltag)."')")) echo '<br>Successfully added '.$ltag.' to '.$_POST['flname'].'.'; else echo '<br>Cannot add '.$ltag.' to '.$_POST['flname'].'. Reason: '.mysql_error(); else echo '<br>Cannot add '.$ltag.' to '.$_POST['flname'].'. Reason: Tag already exists!'; Please help me out Shabbir. Thanks Regards, Praveen Kumar, CEO & Managing Director, PraveenTech Research Labs Research Labs »« University Blog »« TechVidhya »« Forums »« Projects
MySQL Engine you are using to create tables is InnoDB so you can create relationship but I guess having unique on the field will do it for you
Sir, How does UNIQUE help in it? Because, another CID also can have the same Facebook Entry right? You got the logic? Does InnoDB / MyISAM have different syntax? Can you say for both, coz in Windows, I use InnoDB and in Linux I can use only MyISAM! Praveen.
MyISAM does not support foreign key concept and is much faster. At least that was the case sometime back in MySQL 4 and I am sure it continues in MySQL 5 as well but is not 100% sure. Now about your issue I see that your name should be unique and so having unique should do it. Let me know if I understand it right
Sir, Code: TID CID Name 1 1 Wordpress 2 1 Facebook 3 1 Facebook - This should not come! 4 2 Facebook - This is allowed So, CID and Name, combined together, uniquely identifies the record. Got it? How to achieve this? I thought of restricting the duplicate entries by this php script: PHP: if(mysql_num_rows("SELECT * FROM `tags` WHERE `cid` = ".$lcid." AND `name` = '".$ltag."';") == 0) if(mysql_query("INSERT INTO `tags` ( `tid` , `cid` , `name` ) VALUES ( NULL , '".$lcid."', '".trim($ltag)."')")) echo '<br>Successfully added '.$ltag.' to '.$_POST['flname'].'.'; else echo '<br>Cannot add '.$ltag.' to '.$_POST['flname'].'. Reason: '.mysql_error();else echo '<br>Cannot add '.$ltag.' to '.$_POST['flname'].'. Reason: Tag already exists!'; But its not working! Please help me.
PHP is always a solution but then add UNIQUE in combination of 2 fields. Check http://dev.mysql.com/doc/refman/5.1/en/create-table.html A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.
Sir, I need something like: UNIQUE (`cid`, `name`); Is that possible? Are you getting my point? Praveen