declaring a key "inline"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

declaring a key "inline"

Post 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)
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post by m3rajk »

it only seems to have an issue on the regular keys.. the ones where the values don't need to be unique
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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?
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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` ) 
);
m3rajk
DevNet Resident
Posts: 1191
Joined: Mon Jun 02, 2003 3:37 pm

Post 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
Post Reply