[SOLVED] Creating a MySQL table with one column indexed

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
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

[SOLVED] Creating a MySQL table with one column indexed

Post by Bill H »

I have studied the MySQL documentation at length, and I simply cannot comprehend the syntax for creating a key/index (which I believe are the same thing, but have become so confused that I'm no longer even certain of that) when creating a table. I did successfully create a primary key on an INT field which is AUTO-INCREMENT, but in addition I need to have an index of the non-unique variety on a date field. Here are some of the things I have tried, all of which failed:

Code: Select all

<?php
$Query .= "id INT(6) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,";
// the above is the one that worked okay, just fyi
$Query .= " Date DATE KEY DEFAULT '0000-00-00',";
$Query .= " Date DATE INDEX DEFAULT '0000-00-00',";
$Query .= " Date DATE KEY(Date) DEFAULT '0000-00-00',";
$Query .= " Date DATE INDEX(Date) DEFAULT '0000-00-00',";
?>
When I leave out the key/index issue

Code: Select all

<?php
$Query .= " Date DATE DEFAULT '0000-00-00',";
?>
the table is created successfully. The MySQL docs discuss quite a lot about how the indexes work internally, but I can find essentially nothing about the syntax for actually creating them. A search of this board turned up a bunch of samples that were not working, but I didn't find any that were. (Some people said they solved the problem, but didn;t post the solution.)

Any help will be appreciated, as the past hour+ has been spent in fruitless effort.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

//..............
$Query .= " `Date` DATE default '0000-00-00', ";
$Query .= " KEY(`Date`), ";
//..............
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

That is so simple and clear. Thanks.
Post Reply