My first look at PHPMyAdmin / MySQL

[KULP]

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!

[Old Pedant]

Sounds to me like PHPMyAdmin is getting in the way of sanity.

If you simply execute a command to create a table, such as

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

    ALTER TABLE users ADD verified BOOLEAN;

to add the boolean column with, again, PHPMyAdmin not sticking its ugly nose into things, one hopes.

[KULP]

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.

[Fou-Lu]

Originally Posted by KULP

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.

[Old Pedant]

When I use SHOW CREATE TABLE USERS on my created table, I see this:

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!

[Old Pedant]

Originally Posted by Fou-Lu

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:

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.

[Fou-Lu]

Originally Posted by Old Pedant

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:

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.

[Old Pedant]

> 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.

[KULP]

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:

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?

[Old Pedant]

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*:

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:

+-------+--------------------------------------------
| 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 |
+-------+--------------------------------------------

[KULP]

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.

[Old Pedant]

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.

[KULP]

Good explanation. Thanks for all the help!