Re: Create table syntax question

[Michael Stassen]

eifion herbert (IAH-C) wrote:
Thanks for that. I've got it to accept it now, and also realised that
mySQL will let me insert anything I like into columns that are
supposedly foreign keys.
That depends on your table type.  MySQL will enforce your foreign keys in 
tables that support them. (i.e. InnoDB, not MyISAM).

Guess it's a toss up between creating InnoDB tables and the associated
indices, or doing something application side to enforce RI.

-----Original Message-----
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: 16 June 2004 16:43
To: eifion herbert (IAH-C)
Cc: [EMAIL PROTECTED]
Subject: Re: Create table syntax question


eifion herbert (IAH-C) wrote:

Hi,
Probably a very basic question.
I'm trying to a create a table in mySQL 4.0.15 thus:
create table VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
DESC text,
STARTDATE date,
GROUP varchar(25),
CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME), 
CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));

And it says I have a syntax error. I've been through the manual and 
can't spot what I've done wrong. Anyone?
DESC is a reserved word.  Best bet would be to choose a 
different name, but 
if you must name this column DESC, you will have to quote the 
name with 
backticks in the CREATE statement, and every time you use it.

Same goes for GROUP.
You're short a ) at the end.
The CONSTRAINTs will be parsed but silently ignored unless 
you make this an 
InnoDB table by adding TYPE=InnoDB at the end {after the last 
')'}.  If you 
do make it an InnoDB table, you can't make a FOREIGN KEY 
constraint on GROUP 
unless both GROUP and GROUPS.GPNAME are indexed.

So, assuming GROUPS.GPNAME is already indexed and you want to 
keep the 
reserved words DESC and GROUP as column names, you'd need

  CREATE TABLE VACANCIES(
  VACREF char(6) NOT NULL PRIMARY KEY,
  TITLE varchar(60),
  LOC varchar(9),
  `DESC` text,
  STARTDATE date,
  `GROUP` varchar(25),
  INDEX gp_idx (`GROUP`),
  CONSTRAINT fk_grp FOREIGN KEY (`GROUP`) REFERENCES GROUPS(GPNAME),
  CONSTRAINT chk_loc CHECK (LOC IN ('Loc1', 'Loc2', 'Loc3')))
  TYPE = InnoDB;
Michael



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