1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help with using subkey in MySQL

Discussion in 'MySQL' started by praveenscience, Oct 11, 2010.

  1. praveenscience

    praveenscience New Member

    Joined:
    Oct 11, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    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
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    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
     
  3. praveenscience

    praveenscience New Member

    Joined:
    Oct 11, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    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.
     
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    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
     
  5. praveenscience

    praveenscience New Member

    Joined:
    Oct 11, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    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.
     
  6. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    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.
     
  7. praveenscience

    praveenscience New Member

    Joined:
    Oct 11, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    Sir,
    I need something like: UNIQUE (`cid`, `name`); Is that possible? Are you getting my point?

    Praveen
     
  8. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    Yes I got your point and that is what I am suggesting.
     
  9. praveenscience

    praveenscience New Member

    Joined:
    Oct 11, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    Solved using this code! UNIQUE KEY `Index` (`uid` , `name`);
     
  10. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,287
    Likes Received:
    364
    Trophy Points:
    83
    Great.
     

Share This Page