Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   Help with using subkey in MySQL (http://www.go4expert.com/forums/help-using-subkey-mysql-t23543/)

praveenscience 11Oct2010 08:28

Help with using subkey in MySQL
 
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

shabbir 11Oct2010 10:05

Re: Help with using subkey in MySQL
 
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 11Oct2010 10:08

Re: Help with using subkey in MySQL
 
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 11Oct2010 10:12

Re: Help with using subkey in MySQL
 
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 11Oct2010 10:15

Re: Help with using subkey in MySQL
 
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.

shabbir 11Oct2010 10:17

Re: Help with using subkey in MySQL
 
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 11Oct2010 10:35

Re: Help with using subkey in MySQL
 
Sir,
I need something like: UNIQUE (`cid`, `name`); Is that possible? Are you getting my point?

Praveen

shabbir 11Oct2010 13:58

Re: Help with using subkey in MySQL
 
Yes I got your point and that is what I am suggesting.

praveenscience 15Oct2010 15:16

Re: Help with using subkey in MySQL
 
Solved using this code! UNIQUE KEY `Index` (`uid` , `name`);

shabbir 15Oct2010 15:28

Re: Help with using subkey in MySQL
 
Quote:

Originally Posted by praveenscience (Post 73745)
Solved using this code! UNIQUE KEY `Index` (`uid` , `name`);

Great.


All times are GMT +5.5. The time now is 08:56.