MySQL error on CREATE TABLE with BOOL column

Question

I have created the following script to set up my MySQL database:

CREATE DATABASE IF NOT EXISTS magicc_hat;
USE magicc_hat;

CREATE TABLE people (
  personID INT NOT NULL AUTO_INCREMENT,
  firstName VARCHAR(45) NOT NULL,
  lastName VARCHAR(45),
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (personID)
);

CREATE TABLE categories (
  categoryID INT NOT NULL AUTO_INCREMENT,
  categoryName VARCHAR(45) NOT NULL,
  description TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (categoryID)
);

CREATE TABLE homes (
  homeID INT NOT NULL AUTO_INCREMENT,
  homeName VARCHAR(45) NOT NULL,
  notes TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (homeID)
};

CREATE TABLE items (
  itemID INT NOT NULL AUTO_INCREMENT,
  itemName VARCHAR(100) NOT NULL,
  identifier VARCHAR(100),
  quantity INT NOT NULL,
  categoryID INT NOT NULL,
  homeID INT NOT NULL,
  itemStatus ENUM('normal', 'broken', 'missing') NOT NULL DEFAULT 'normal',
  description TEXT,
  image VARCHAR(45),
  notes TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (itemID),
  FOREIGN KEY (categoryID) REFERENCES categories(categoryID),
  FOREIGN KEY (homeID) REFERENCES homes(homeID)
};

CREATE TABLE projects (
  projectID INT NOT NULL AUTO_INCREMENT,
  projectName VARCHAR(45) NOT NULL,
  description TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (projectID)
);

CREATE TABLE checkouts (
  checkoutID INT NOT NULL AUTO_INCREMENT,
  itemID INT NOT NULL,
  personID INT NOT NULL,
  projectID INT,
  quantity INT NOT NULL,
  outDateTime DATETIME,
  inDateTime DATETIME,
  outNotes TEXT,
  inNotes TEXT,
  checkedIn BOOL,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (checkoutID),
  FOREIGN KEY (itemID) REFERENCES items(itemID),
  FOREIGN KEY (personID) REFERENCES people(personID),
  FOREIGN KEY (projectID) REFERENCES projects(projectID)
);

However, when I run this script I get the following error messages:

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 '}' at line 8

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 '}' at line 17

ERROR 1215 (HY000): Cannot add foreign key constraint

I suspect it has something to do with using the bool datatype since that is what is happening at lines 8 and 17; however I have not been able to find any solutions to this issue online. Is someone able to see what might be causing the problem? I'm running MySQL 5.6.11 on my local Windows machine.

Thanks!

Answer 1

you have 2 syntax errors as below... And since below tables are not created, you are facing foreign key error.. run below sqls your problem will be solved :)

CREATE TABLE homes (
  homeID INT NOT NULL AUTO_INCREMENT,
  homeName VARCHAR(45) NOT NULL,
  notes TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (homeID)
); /* previously }; */

CREATE TABLE items (
  itemID INT NOT NULL AUTO_INCREMENT,
  itemName VARCHAR(100) NOT NULL,
  identifier VARCHAR(100),
  quantity INT NOT NULL,
  categoryID INT NOT NULL,
  homeID INT NOT NULL,
  itemStatus ENUM('normal', 'broken', 'missing') NOT NULL DEFAULT 'normal',
  description TEXT,
  image VARCHAR(45),
  notes TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (itemID),
  FOREIGN KEY (categoryID) REFERENCES categories(categoryID),
  FOREIGN KEY (homeID) REFERENCES homes(homeID)
); /* previously }; */

Answer 2

Try this one, I tested it and it created the database successfully. The issues were caused due to the opening and closing brackets not matching each other.

CREATE DATABASE IF NOT EXISTS magicc_hat;
USE magicc_hat;

CREATE TABLE people (
  personID INT NOT NULL AUTO_INCREMENT,
  firstName VARCHAR(45) NOT NULL,
  lastName VARCHAR(45),
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (personID)
);

CREATE TABLE categories (
  categoryID INT NOT NULL AUTO_INCREMENT,
  categoryName VARCHAR(45) NOT NULL,
  description TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (categoryID)
);

CREATE TABLE homes (
  homeID INT NOT NULL AUTO_INCREMENT,
  homeName VARCHAR(45) NOT NULL,
  notes TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (homeID)
);

CREATE TABLE items (
  itemID INT NOT NULL AUTO_INCREMENT,
  itemName VARCHAR(100) NOT NULL,
  identifier VARCHAR(100),
  quantity INT NOT NULL,
  categoryID INT NOT NULL,
  homeID INT NOT NULL,
  itemStatus ENUM('normal', 'broken', 'missing') NOT NULL DEFAULT 'normal',
  description TEXT,
  image VARCHAR(45),
  notes TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (itemID),
  FOREIGN KEY (categoryID) REFERENCES categories(categoryID),
  FOREIGN KEY (homeID) REFERENCES homes(homeID)
);

CREATE TABLE projects (
  projectID INT NOT NULL AUTO_INCREMENT,
  projectName VARCHAR(45) NOT NULL,
  description TEXT,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (projectID)
);

CREATE TABLE checkouts (
  checkoutID INT NOT NULL AUTO_INCREMENT,
  itemID INT NOT NULL,
  personID INT NOT NULL,
  projectID INT,
  quantity INT NOT NULL,
  outDateTime DATETIME,
  inDateTime DATETIME,
  outNotes TEXT,
  inNotes TEXT,
  checkedIn BOOL,
  archived BOOL NOT NULL DEFAULT '0',

  PRIMARY KEY (checkoutID),
  FOREIGN KEY (itemID) REFERENCES items(itemID),
  FOREIGN KEY (personID) REFERENCES people(personID),
  FOREIGN KEY (projectID) REFERENCES projects(projectID)
);

Answer 3

Get rid of the curly braces and replace with (

Works great : http://sqlfiddle.com/#!8/4ad37

Answer 4

The characters need to be the same. Use parentheses for both

CREATE TABLE homes (

};

In this case: It's showing you the incorrect character in the error message:

check the manual that corresponds to your MySQL server version for the right
syntax to use near '}' at line 8