[praveenscience]

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.

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.

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:

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

[shabbir]

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

[praveenscience]

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.

[shabbir]

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

[praveenscience]

Sir,

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.

[shabbir]

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.

[praveenscience]

Sir,
I need something like: UNIQUE (`cid`, `name`); Is that possible? Are you getting my point?

Praveen

[shabbir]

Yes I got your point and that is what I am suggesting.

[praveenscience]

Solved using this code! UNIQUE KEY `Index` (`uid` , `name`);

[shabbir]

Quote:

Originally Posted by praveenscience

Solved using this code! UNIQUE KEY `Index` (`uid` , `name`);

Great.