Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts

    My first look at PHPMyAdmin / MySQL

    I'm messing around with databases and tables and I made a table names 'users' with columns like userid (primary & auto increment), username, etc, and I have one called verified which i set as a boolean (tinyint) and default value defined to 0. The problem is when I made this column it automatically tried to make it a primary key and require unique. I obviously don't want either of the 2 to be the case but it won't let me switch it! If I go in and edit the column and change it to text, however, it doesn't assume this PK value or the other things. I'm really confused why this keeps happening. Any ideas?

    Thanks!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    Sounds to me like PHPMyAdmin is getting in the way of sanity.

    If you simply execute a command to create a table, such as
    Code:
    create table users ( 
        userid int auto_increment primary key, 
        verified boolean, 
        name varchar(30) 
    );
    it works perfectly okay.

    So... Forget creating the table using the table creation tools of PHPMyAdmin and just issue a query, as above.

    Or you could create the table without the verified column and then use the MySQL query/command
    Code:
        ALTER TABLE users ADD verified BOOLEAN;
    to add the boolean column with, again, PHPMyAdmin not sticking its ugly nose into things, one hopes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    It still creates the column with unique and primary key required... I don't understand, especially because I already have a PK set. GAH

    I even tried to create a new table with your first code and just changed the table name to users2 and this still happens:



    EDIT: Wait, does gold key mean not primary? I'm getting really confused.
    Last edited by KULP; 11-30-2012 at 05:42 PM.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by KULP View Post
    EDIT: Wait, does gold key mean not primary? I'm getting really confused.
    I'd guess so. Since using an autoincrement requires that the PK be in use, and that PK is not in gold, that would suggest that phpmyadmin uses gold to mark what is not a pk.
    I haven't used phpmyadmin in a long long time. You could simply issue a SHOW CREATE TABLE yourtablename query and it will give you the text required to create it. That would indicate all your keys as well.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    When I use SHOW CREATE TABLE USERS on my created table, I see this:
    Code:
    mysql> show create table users;
    +-------+-------------------------------------------
    | Table | Create Table
    +-------+-------------------------------------------
    | users | CREATE TABLE `users` (
      `userid` int(11) NOT NULL AUTO_INCREMENT,
      `verified` tinyint(1) DEFAULT NULL,
      `name` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`userid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+-------------------------------------------
    Man, your experience sure make me want to learn PHPMyAdmin....NOT!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    Quote Originally Posted by Fou-Lu View Post
    Since using an autoincrement requires that the PK be in use...
    Not true! AUTO_INCREMENT requires that the AUTO_INCREMENT column have a a UNIQUE INDEX, but it does *NOT* have to be the primary key.

    Example:
    Code:
    mysql> CREATE TABLE FouLu ( id INT AUTO_INCREMENT,
        -> email VARCHAR(50) PRIMARY KEY,
        -> UNIQUE INDEX id_index (id)
        -> );
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> show create table FouLu;
    +-------+----------------------------------------------------
    | Table | Create Table
    +-------+----------------------------------------------------
    | FouLu | CREATE TABLE `foulu` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(50) NOT NULL,
      PRIMARY KEY (`email`),
      UNIQUE KEY `id_index` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+----------------------------------------------------
    I grant you that this is not normal usage, but it's perfectly legal.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by Old Pedant View Post
    Not true! AUTO_INCREMENT requires that the AUTO_INCREMENT column have a a UNIQUE INDEX, but it does *NOT* have to be the primary key.

    Example:
    Code:
    mysql> CREATE TABLE FouLu ( id INT AUTO_INCREMENT,
        -> email VARCHAR(50) PRIMARY KEY,
        -> UNIQUE INDEX id_index (id)
        -> );
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> show create table FouLu;
    +-------+----------------------------------------------------
    | Table | Create Table
    +-------+----------------------------------------------------
    | FouLu | CREATE TABLE `foulu` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(50) NOT NULL,
      PRIMARY KEY (`email`),
      UNIQUE KEY `id_index` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+----------------------------------------------------
    I grant you that this is not normal usage, but it's perfectly legal.
    I'll be damned, this whole time I thought that you required PK for ai in mysql!
    Would there be any benefit of doing the above instead versus swapping the PK and UK on that table? I typically don't use AI at all as I don't usually use a surrogate key, but this may become beneficial in the future for an alternate field I would want to increment.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    > Would there be any benefit of doing the above instead versus swapping the PK and UK on that table?

    Probably not much on that table.

    But sometimes you will want a primary key that uses multiple fields.

    And, at least with INNODB (and, by the by, with SQL Server!) that has PHYSICAL implications:
    http://dev.mysql.com/doc/refman/5.5/...mary-keys.html
    With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.
    In other words, the primary key is placed FIRST in the record layout *AND* forms the basis of the B-TREE organization of the entire table.

    SQL Server takes great pains to emphasize that choosing your primary key can make a huge difference in performance. MySQL isn't quite so sensitive, but it can still make a noticeable difference.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    Still really confused, i just started over with a new table and I can't change primary keys or anything. Ugh!

    I did show table and this is what I get:

    Code:
    CREATE TABLE `users` (
     `userid` int(11) NOT NULL AUTO_INCREMENT,
     `uname` text NOT NULL,
     `pword` text NOT NULL,
     `email` text NOT NULL,
     `nickname` text NOT NULL,
     `verified` tinyint(1) NOT NULL DEFAULT '0',
     PRIMARY KEY (`userid`),
     UNIQUE KEY `userid` (`userid`),
     KEY `userid_2` (`userid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    I *think* the query looks correct (except what is "KEY `userid_2` (`userid`)")... But this is what it actually shows:


    Is anyone else confused?
    Last edited by KULP; 11-30-2012 at 10:41 PM.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    That is *REALLY* what you got from SHOW CREATE TABLE users??

    Then the display in PHPMyAdmin? Then I think you need to remove PHPMyAdmin from your system and re-install it.

    Because it's clearly braindead.

    HOWEVER...

    You must have also done something wrong when you did the CREATE TABLE, in the first place.

    You have THREE indexes all defined on the field userid which makes no sense at all.

    On top of that, you are using the TEXT data type which is a VERY expensive data type for no reason at all! Not just once by FOUR times!

    Try doing *JUST THIS*:
    Code:
    CREATE TABLE users (
        userid INT AUTO_INCREMENT PRIMARY KEY,
        uname VARCHAR(30) NOT NULL,
        pword VARCHAR(30) NOT NULL,
        email VARCHAR(200) NOT NULL,
        nickname VARCHAR(30) NOT NULL,
        verified BOOLEAN NOT NULL DEFAULT 0
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    When I do that and then do show create table users I get:
    Code:
    +-------+--------------------------------------------
    | Table | Create Table
    +-------+--------------------------------------------
    | users | CREATE TABLE `users` (
      `userid` int(11) NOT NULL AUTO_INCREMENT,
      `uname` varchar(30) NOT NULL,
      `pword` varchar(30) NOT NULL,
      `email` varchar(200) NOT NULL,
      `nickname` varchar(30) NOT NULL,
      `verified` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`userid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+--------------------------------------------
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    KULP (12-02-2012)

  • #11
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    Wow that was quite the process. Not sure what all went down but I must say I find it strange that the PK is denoted by a silver key. Seems as though it should be the gold. Anyways... I was clearly pressing SOMETHING wrong.. although I'm not sure what, I think it was successful this time. Really was not looking forward to re-installing. Does this look right to you too (the show turned up identical)? Also, does setting a value at the PK automatically set it as UNIQUE? Because it doesn't show unique I don't think.


  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,111
    Thanks
    75
    Thanked 4,336 Times in 4,302 Posts
    VOLKSWAGENS are CARS by definition. CARS are not necessarily VOLKSWAGENS. Yet we don't say "VOLKSWAGEN CARS" in normal usage.

    PRIMARY keys are UNIQUE by definition. UNIQUE keys are not necessarily PRIMARY. We don't say PRIMARY UNIQUE in normal usage.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Regular Coder
    Join Date
    Mar 2012
    Posts
    168
    Thanks
    5
    Thanked 11 Times in 11 Posts
    Good explanation. Thanks for all the help!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •