Foreign Key Problem

[protagonist]

Now, in my database, I'm trying to include foreign keys. An example:

CREATE TABLE Person(PersonID INTEGER PRIMARY KEY AUTO_INCREMENT, FOREIGN KEY(Owner_OwnerID) REFERENCES Owner(OwnerID) ON DELETE CASCADE, Name VARCHAR(20), Job VARCHAR(20), DOB DATE, StartEmploymentDate DATE,EndEmploymentDate DATE, BiographicalInfo TEXT)
;

which works fine. Except that the foreign key column doesn't appear.

Now, according to the documentation it's because it needs to be an InnoDB, which can be done by putting ENGINE=InnoDB after the create table end bracket and before the semi-colon.

And now it refuses to work. I get
ERROR 1005: Can't create table './history_db/person.frm' (errno: 150)

eh?

 

[TonyGroves]

According to my docs, the syntax is TYPE=INNODB

-----
ALTER world DROP injustice, ADD peace;

 

[protagonist]

That gives the exact same result, I'm afraid...

 

[gcaramia]

Try:

CREATE TABLE `person` (
  personid int(11) NOT NULL AUTO_INCREMENT,
  `ownerid` int(11) NOT NULL DEFAULT '0',
  name varchar(20),
  job varchar(20),
  dob date,
  startemploymentdate date,
  biographicalinfo text,
  PRIMARY KEY(`personid`),
  INDEX `FKIndex1`(`ownerid`),
  FOREIGN KEY `Reference_01`(`ownerid`)
    REFERENCES `owner`(`ownerid`)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB
ROW_FORMAT=default;

Giovanni Caramia