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]
