Re: syntax error on create

2004-04-12 Thread Victoria Reznichenko 
"warwick mayson" <[EMAIL PROTECTED]> wrote:
> 
> I am new to mysql and have a create script that when sourced throws a syntax error.
> 
> The script : 
> 
> CREATE TABLE employee (
>  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>  name VARCHAR(255) NOT NULL,
>  PRIMARY KEY(id)
> )
> TYPE=InnoDB;
> 
> CREATE TABLE position (
>  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>  position_type INTEGER UNSIGNED NULL,
>  PRIMARY KEY(id)
> )
> TYPE=InnoDB;
> 
> CREATE TABLE company (
>  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>  name VARCHAR(255) NULL,
>  PRIMARY KEY(id)
> )
> TYPE=InnoDB;
> 
> CREATE TABLE company_position (
>  company_id INTEGER UNSIGNED NOT NULL,
>  position_id INTEGER UNSIGNED NOT NULL,
>  PRIMARY KEY(company_id, position_id),
>  INDEX company_id_FKIndex1(company_id),
>  INDEX position_id_FKIndex2(position_id),
>  FOREIGN KEY(company_id)
>REFERENCES company(id)
>  ON DELETE NO ACTION
>  ON UPDATE NO ACTION,
>  FOREIGN KEY(position_id)
>REFERENCES position(id)
>  ON DELETE NO ACTION
>  ON UPDATE NO ACTION
> )
> TYPE=InnoDB;
> 
> returns :
> 
> ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds 
> to your MySQL server version for the right syntax to use near 'position(id)
> 
>  ON DELETE NO ACTION
>  ON UPDATE NO ACT
> 
> Can anyone explain why this is happening ???
> 

MySQL has a function with name POSITION.
You should quote table name with backticks (i.e. REFERENCES `position`(`id`) ..) or 
add space between table name and bracket (i.e. REFERENCES position (id) .. )


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

syntax error on create

2004-04-10 Thread warwick mayson 

hi

I am new to mysql and have a create script that when sourced throws a syntax error.

The script : 

CREATE TABLE employee (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

CREATE TABLE position (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  position_type INTEGER UNSIGNED NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

CREATE TABLE company (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NULL,
  PRIMARY KEY(id)
)
TYPE=InnoDB;

CREATE TABLE company_position (
  company_id INTEGER UNSIGNED NOT NULL,
  position_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(company_id, position_id),
  INDEX company_id_FKIndex1(company_id),
  INDEX position_id_FKIndex2(position_id),
  FOREIGN KEY(company_id)
REFERENCES company(id)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
  FOREIGN KEY(position_id)
REFERENCES position(id)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
)
TYPE=InnoDB;

returns :

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds 
to your MySQL server version for the right syntax to use near 'position(id)

  ON DELETE NO ACTION
  ON UPDATE NO ACT

Can anyone explain why this is happening ???

Thanks in advance.

Warwick




Find what you are looking for with the Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]