Primary Key Statement

------=_NextPart_000_0004_01C50276.305DD760
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I have been having problems as the releases of MySQL have changed. This was
allowed at one time. I could always put the primary key at the end like
this.

$sql = "CREATE TABLE IF NOT EXISTS sotext ("
. " solang_id INT(2) NOT NULL, "
. " sotext_id CHAR(2) NOT NULL, "
. " sotext_text VARCHAR(255) NOT NULL "
. " )"
. " PRIMARY KEY(solang_id) "
. " TYPE = MYISAM ";

But it seems now that I can only put the primary key in the filed that is
the key. Like this.

$sql = "CREATE TABLE IF NOT EXISTS sotext ("
. " solang_id I NT(2) NOT NULL PRIMARY
KEY, "
. " sotext_id CHAR(2) NOT NULL, "
. " sotext_text VARCHAR(255) NOT NULL "
. " )"
. " TYPE = MYISAM ";

Now I want to create a primary key across two columns. The following SQL
statement fails. I am in a Win XP SP2 MySQL 4.1.8 environemnt. I tried to
turn debug on but it failed for some reason. I need a little education right
now anyway. Why is the following failing? How can I create a Primary Key
across 2 columns? How can I create an Index across 2 columns?

$sql = "CREATE TABLE IF NOT EXISTS sotext ("
. " solang_id INT(2) NOT NULL, "
. " sotext_id CHAR(2) NOT NULL, "
. " sotext_text VARCHAR(255) NOT NULL "
. " )"
. " PRIMARY KEY(solang_id, sotext_id) "
. " TYPE = MYISAM ";

All help is appreciated. Thanks in advance.

Mike



------=_NextPart_000_0004_01C50276.305DD760
Content-Type: text/plain; charset=us-ascii

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org
------=_NextPart_000_0004_01C50276.305DD760--

Re: Primary Key Statement

Your syntax is incorrect, but look at this:

CREATE TABLE IF NOT EXISTS sotext (
solang_id INT(2) NOT NULL,
sotext_id CHAR(2) NOT NULL,
sotext_text VARCHAR(255) NOT NULL,
PRIMARY KEY(solang_id, sotext_id)
) TYPE = MYISAM;

This works. You closed the column definitions with a ) before you
defined the primary key, which needs to be part of the column
definitions, coming after the last column is defined, and separated from
it by a comma.

Mike


Michael Avila wrote:
> I have been having problems as the releases of MySQL have changed. This was
> allowed at one time. I could always put the primary key at the end like
> this.
>
> $sql = "CREATE TABLE IF NOT EXISTS sotext ("
> . " solang_id INT(2) NOT NULL, "
> . " sotext_id CHAR(2) NOT NULL, "
> . " sotext_text VARCHAR(255) NOT NULL "
> . " )"
> . " PRIMARY KEY(solang_id) "
> . " TYPE = MYISAM ";
>
> But it seems now that I can only put the primary key in the filed that is
> the key. Like this.
>
> $sql = "CREATE TABLE IF NOT EXISTS sotext ("
> . " solang_id I NT(2) NOT NULL PRIMARY
> KEY, "
> . " sotext_id CHAR(2) NOT NULL, "
> . " sotext_text VARCHAR(255) NOT NULL "
> . " )"
> . " TYPE = MYISAM ";
>
> Now I want to create a primary key across two columns. The following SQL
> statement fails. I am in a Win XP SP2 MySQL 4.1.8 environemnt. I tried to
> turn debug on but it failed for some reason. I need a little education right
> now anyway. Why is the following failing? How can I create a Primary Key
> across 2 columns? How can I create an Index across 2 columns?
>
> $sql = "CREATE TABLE IF NOT EXISTS sotext ("
> . " solang_id INT(2) NOT NULL, "
> . " sotext_id CHAR(2) NOT NULL, "
> . " sotext_text VARCHAR(255) NOT NULL "
> . " )"
> . " PRIMARY KEY(solang_id, sotext_id) "
> . " TYPE = MYISAM ";
>
> All help is appreciated. Thanks in advance.
>
> Mike
>
>
>

--
Mike Hillyer, Technical Writer
MySQL AB, www.mysql.com
Office: +1 403-380-6535
Mobile: +1 403-330-0870

MySQL User Conference (Santa Clara CA, 18-21 April 2005)
Early registration until February 28: www.mysqluc.com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org