Setting up FK's

[dazman]

Hi, I am using InnoDB and I am having trouble with the MySQL syntax. I have checked the manual but what they have written doesnt actually seem to work.

I am actually writing a script to create all my tables at once, so when i install my web app I am making, It creates all the tables it needs.

 

Here is my create table string.

 

CREATE TABLE `bob`.`address` (`Address_ID` VARCHAR(12) NOT NULL, `Address_Line1` VARCHAR(45), `Address_Line2` VARCHAR(45), `Address_Line3` VARCHAR(45), `ZipPostalCode` VARCHAR(10) NOT NULL, `City` VARCHAR(45) NOT NULL, `State` VARCHAR(45), `Country` VARCHAR(45) NOT NULL, PRIMARY KEY (`Address_ID`), CONSTRAINT `FK_Address_1` FOREIGN KEY `FK_Address_1` REFERENCES `country` (`Country`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB;

 

 

 

 

 

I am getting this error:

ERROR 1064 (42000): 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 'REFERENCES `country` (`Country`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE =' at line 1

 

country is my table name and Country ( with a capital) is my field name. All my table names have lower case letters at the start. but my fields do not, (just the way I have gone about doing things on day one when I started). I reckon I have made a syntax problem, but it may be the way I have set up the foreign key. Any help would be appreciated.

 

Also how does my statement know that FK_Address_1 (what I am calling my foreign key) relates to the Country field in the table I am trying to create? Obviously the country table already exists with country.Country field being a PK.

[RichardRotterdam]

I indendented your create query for better readability

CREATE TABLE `bob`.`address`
(
  `Address_ID` VARCHAR(12) NOT NULL,
  `Address_Line1` VARCHAR(45),
  `Address_Line2` VARCHAR(45),
  `Address_Line3` VARCHAR(45),
  `ZipPostalCode` VARCHAR(10) NOT NULL,
  `City` VARCHAR(45) NOT NULL,
  `State` VARCHAR(45),
  `Country` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Address_ID`),
  CONSTRAINT `FK_Address_1` FOREIGN KEY `FK_Address_1` REFERENCES `country` (`Country`)
  ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB;

 

Ok here's my critique on it:

 

`Address_ID` VARCHAR(12) NOT NULL

Why is your adress_id of a varchar type? Is there a reason for this?

 

CONSTRAINT `FK_Address_1` FOREIGN KEY `FK_Address_1` REFERENCES `country` (`Country`)

You could use the CONSTRAINT `FK_Address_1` but you don't really need it. It's just a name added to your constraint.

 

FOREIGN KEY `FK_Address_1`

Here you are trying to create a foreign key on the FK_adress_1 field, howerver you dont have a field named `FK_Address_1`. Also it's common for the foreign key type to be an int is there a reason your using varchar here?

 

 

REFERENCES `country` (`Country`)

Here you are refering to the field named `Country` within table named `country`

I recommed refereing to an id field such as country_id

 

Here is how I would do it

CREATE TABLE `bob`.`addresses`
(
  `Address_id`     INT(12) NOT NULL AUTO_INCREMENT,
  `country_id`     INT(12) NOT NULL,
  `Address_Line1`  VARCHAR(45),
  `Address_Line2`  VARCHAR(45),
  `Address_Line3`  VARCHAR(45),
  `ZipPostalCode`  VARCHAR(10) NOT NULL,
  `City`           VARCHAR(45) NOT NULL,
  `State`          VARCHAR(45),

  PRIMARY KEY (`Address_ID`),
  FOREIGN KEY (`country_id`)
    REFERENCES `countries`(`country_id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

 

edit

You might also want to set up a foreign key for the states and the cities.