Page 1 of 1

declaring a key "inline"

Posted: Wed Aug 13, 2003 3:19 pm
by m3rajk
mysql> CREATE TABLE users(
-> uid int unsigned NOT NULL auto_increment PRIMARY KEY,
-> username varchar(15) NOT NULL UNIQUE KEY,
-> password varchar(15) NOT NULL,
-> email tinytext NOT NULL UNIQUE KEY,
-> gmt_offset tinyint default '-5' NOT NULL,
-> site_access tinyint unsigned default '0' NOT NULL,
-> admin_comment tinytext,
-> approved tinyint unsigned default '12' NOT NULL,
-> last_login_ip tinytext NOT NULL,
-> last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
-> end_level date default '0000-00-00' NOT NULL,
-> login_duration tinyint default '0' NOT NULL,
-> enroll datetime default '0000-00-00 00:00:00' NOT NULL KEY,
-> gender char(1) default 'F' NOT NULL KEY,
-> last_activity datetime default '0000-00-00 00:00:00' NOT NULL
-> ) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'KEY,
gender char(1) default 'F' NOT NULL KEY,
last_activity datetime default '00' at line 14
mysql>
enroll needs to be a key.one page loads based on when ppl joined (enrolled)

Posted: Wed Aug 13, 2003 3:41 pm
by nielsene
What do you means by "key"

If you mean that enroll is a "candidate key" than UNIQUE [KEY] NOT NULL, should be sufficient. If you mean indexed, then I'm not sure what the appropriate MySQLism is for that.

Posted: Wed Aug 13, 2003 3:46 pm
by m3rajk
it only seems to have an issue on the regular keys.. the ones where the values don't need to be unique

Posted: Wed Aug 13, 2003 9:55 pm
by nielsene
Umm, what do you mean by a key that doesn't need to be unique???

I'll admit I'm clueless about MySQL, so if its some nonstandard concept it uses I'll be useless, but the very notion of a key in database systems implies uniqueness....

A key is a collection of columns (possibly/normally a single column) that can uniquely identify a row in the host table. What else could a key mean?

Posted: Wed Aug 13, 2003 11:06 pm
by m3rajk
the oplain use of key in mysql is synonymouswith index. what sql sets a faster way to search by so that it's more efficient.. i'm trying to make all those that are likely to be used often set as keys o that it will index them.

unique keys can only have one item in that row with that value, but can be null unless otherwise stated. primary keys are unique keys that cannot be null

Posted: Wed Aug 13, 2003 11:07 pm
by m3rajk
on top of that, apprently it only allows declaration of primary keys on the line like that. yet the book i have has an example with a unique key in line like that

Posted: Thu Aug 14, 2003 6:37 am
by Coco
according to phpmyadmin:

Code: Select all

ALTER TABLE `table` ADD INDEX ( `thing` )
and also

Code: Select all

CREATE TABLE `sdfadf` (
`a` TINYINT( 2 ) NOT NULL ,
`b` TINYINT( 2 ) NOT NULL ,
PRIMARY KEY ( `a` ) ,
INDEX ( `b` ) 
);

Posted: Thu Aug 14, 2003 2:58 pm
by m3rajk
the book i have has unique keys declared inline. the mysql online manuall only has primary keys. some testing looks like only primary keys can be done inline in 3.23.56