Can someone help look at this code to figure out why the database cannot be created?
Some tables repeatedly fail to create even though everything has been double-checked to be correct
DROP SCHEMA IF EXISTS `db_system` ;
CREATE SCHEMA IF NOT EXISTS `db_system` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `db_system` ;
DROP TABLE IF EXISTS `staff_role`;
CREATE TABLE IF NOT EXISTS `staff_role` (
`staff_role_id` int(11) NOT NULL AUTO_INCREMENT,
`staff_role` varchar(45) DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`staff_role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `property_type` ;
CREATE TABLE IF NOT EXISTS `property_type` (
`property_type_id` int(11) NOT NULL AUTO_INCREMENT,
`property_type` varchar(45) DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`property_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `user_group` ;
CREATE TABLE IF NOT EXISTS `user_group` (
`user_group_id` int(11) NOT NULL AUTO_INCREMENT,
`user_group_name` varchar(45) DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`user_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `permissions` ;
CREATE TABLE IF NOT EXISTS `permissions` (
`permissions_id` int(11) NOT NULL AUTO_INCREMENT,
`permission` varchar(45) DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`permissions_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `usergroup_permissions` ;
CREATE TABLE IF NOT EXISTS `usergroup_permissions` (
`usergroup_permissions_id` int(11) NOT NULL AUTO_INCREMENT,
`permissions_id` int(11) DEFAULT NULL,
`usergroup_id` int(11) DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`usergroup_permissions_id`),
KEY `permissions_id` (`permissions_id`),
KEY `usergroup_id` (`usergroup_id`),
CONSTRAINT `permissions_id` FOREIGN KEY (`permissions_id`) REFERENCES `permissions` (`permissions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `usergroup_id` FOREIGN KEY (`usergroup_id`) REFERENCES `usergroup` (`usergroup_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `staff` ;
CREATE TABLE IF NOT EXISTS `staff` (
`staff_id` int(11) NOT NULL AUTO_INCREMENT,
`staff_role_id` int(11) DEFAULT NULL,
`firstname` varchar(45) DEFAULT NULL,
`middlename` varchar(45) DEFAULT NULL,
`surname` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`phone` int(15) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`staff_id`),
KEY `staff_role_id` (`staff_role_id`),
CONSTRAINT `staff_role_id` FOREIGN KEY (`staff_role_id`) REFERENCES `staff_role` (`staff_role_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `staff_user` ;
CREATE TABLE IF NOT EXISTS `staff_user` (
`staff_user_id` int(11) NOT NULL AUTO_INCREMENT,
`usergroup_id` int(11) DEFAULT NULL,
`staff_id` int(11) DEFAULT NULL,
`username` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`staff_user_id`),
KEY `usergroup_id` (`usergroup_id`),
KEY `staff_id` (`staff_id`),
CONSTRAINT `usergroup_id` FOREIGN KEY (`usergroup_id`) REFERENCES `user_group` (`user_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `staff_id` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `landlord` ;
CREATE TABLE IF NOT EXISTS `landlord` (
`landlord_id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(45) DEFAULT NULL,
`middlename` varchar(45) DEFAULT NULL,
`surname` varchar(45) DEFAULT NULL,
`address` varchar(80) DEFAULT NULL,
`town` varchar(45) DEFAULT NULL,
`postcode` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`phone` int(15) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`staff_id` int(11) DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`landlord_id`),
KEY `staff_id` (`staff_id`),
CONSTRAINT `staff_id` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `property` ;
CREATE TABLE IF NOT EXISTS `property` (
`property_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(45) DEFAULT NULL,
`description` varchar(200) DEFAULT NULL,
`street` varchar(100) DEFAULT NULL,
`town` varchar(45) DEFAULT NULL,
`postcode` varchar(45) DEFAULT NULL,
`property_type_id` int(11) DEFAULT NULL,
`number_of_bedrooms` int(11) DEFAULT NULL,
`monthly_rental_price` decimal(11,2) DEFAULT NULL,
`weekly_rental_price` decimal(11,2) DEFAULT NULL,
`sale_price` decimal(11,2) DEFAULT NULL,
`created_date` date DEFAULT NULL,
`staff_id` int(11) DEFAULT NULL,
`landlord_id` int(11) DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`property_id`),
KEY `staff_id` (`staff_id`),
KEY `landlord_id` (`landlord_id`),
KEY `property_type_id` (`property_type_id`),
CONSTRAINT `staff_id` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `landlord_id` FOREIGN KEY (`landlord_id`) REFERENCES `landlord` (`landlord_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `property_type_id` FOREIGN KEY (`property_type_id`) REFERENCES `property_type` (`property_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `property_photos` ;
CREATE TABLE IF NOT EXISTS `property_photos` (
`property_photos_id` int(11) NOT NULL AUTO_INCREMENT,
`property_id` int(11) DEFAULT NULL,
`photo_url` varchar(200) DEFAULT NULL,
`main_photo` tinyint(1) DEFAULT NULL,
`photo_description` varchar(300) DEFAULT NULL,
PRIMARY KEY (`property_photos_id`),
KEY `property_id` (`property_id`),
CONSTRAINT `property_id` FOREIGN KEY (`property_id`) REFERENCES `property` (`property_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `customer` ;
CREATE TABLE IF NOT EXISTS `customer` (
`customer_id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(45) DEFAULT NULL,
`middlename` varchar(45) DEFAULT NULL,
`surname` varchar(45) DEFAULT NULL,
`address` varchar(80) DEFAULT NULL,
`town` varchar(45) DEFAULT NULL,
`postcode` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`phone` int(15) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`staff_id` int(11) DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`customer_id`),
KEY `staff_id` (`staff_id`),
CONSTRAINT `staff_id` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) DEFAULT NULL,
`username` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`user_id`),
KEY `customer_id` (`customer_id`),
CONSTRAINT `customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `messages` ;
CREATE TABLE IF NOT EXISTS `messages` (
`messages_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`subject` varchar(100) DEFAULT NULL,
`message` varchar(500) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`messages_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `customer_property` ;
CREATE TABLE IF NOT EXISTS `customer_property` (
`customer_property_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) DEFAULT NULL,
`property_id` int(11) DEFAULT NULL,
`saved_date` date DEFAULT NULL,
`status` int(2) DEFAULT NULL,
PRIMARY KEY (`customer_property_id`),
KEY `customer_id` (`customer_id`),
KEY `property_id` (`property_id`),
CONSTRAINT `property_id` FOREIGN KEY (`property_id`) REFERENCES `property` (`property_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
