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.
Guess it's a toss up between creating InnoDB tables and the associated indeces, 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]
