Help with using subkey in MySQL

praveenscience's Avatar, Join Date: Oct 2010
Light Poster
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 - This should not come!
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
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
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
0
praveenscience's Avatar, Join Date: Oct 2010
Light Poster
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.
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
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
0
praveenscience's Avatar, Join Date: Oct 2010
Light Poster
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 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!'
But its not working! Please help me.
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
PHP is always a solution but then add UNIQUE in combination of 2 fields.

Check http://dev.mysql.com/doc/refman/5.1/...ate-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.
0
praveenscience's Avatar, Join Date: Oct 2010
Light Poster
Sir,
I need something like: UNIQUE (`cid`, `name`); Is that possible? Are you getting my point?

Praveen
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Yes I got your point and that is what I am suggesting.
0
praveenscience's Avatar, Join Date: Oct 2010
Light Poster
Solved using this code! UNIQUE KEY `Index` (`uid` , `name`);
0
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Quote:
Originally Posted by praveenscience View Post
Solved using this code! UNIQUE KEY `Index` (`uid` , `name`);
Great.