Hello I'm trying to create some tables when a new user registers in my php site and I'm trying to execute the last query in the transaction and it gives me the following error: #1005 - Can't create table 'user_39.records' (errno: 150)
Could someone please point out what I'm doing wrong? I'm not so familiar with foreign keys this is my first time using them. Thank you.
database tables:
private function createDriversTable($database) {
$query = "CREATE TABLE IF NOT EXISTS $database.drivers (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`surname` varchar(255) NOT NULL,
`car_type` varchar(255) NOT NULL,
`circulation_num` varchar(255) NOT NULL,
`special_card_num` varchar(255) NOT NULL,
`special_card_num_exp` date NOT NULL,
`drivers_license_num` varchar(255) NOT NULL,
`drivers_license_exp` date NOT NULL,
`id_num` varchar(255) NOT NULL,
`vat` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`mobile` bigint(20) NOT NULL,
`radiotaxi_code` bigint(20) NOT NULL,
`languages` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Driver records' AUTO_INCREMENT=1";
return $query;
}
private function createClientsTable($database) {
$query = "CREATE TABLE IF NOT EXISTS $database.clients (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`region` varchar(255) NOT NULL,
`vat` varchar(255) NOT NULL COMMENT 'AFM',
`tax_office` varchar(255) NOT NULL COMMENT 'DOY',
`phone` bigint(20) NOT NULL,
`mobile` bigint(20) NOT NULL,
`email` varchar(255) NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Client records' AUTO_INCREMENT=1";
return $query;
}
private function createCarsTable($database) {
$query = "CREATE TABLE IF NOT EXISTS $database.cars (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`oil_change` date NOT NULL,
`oil_filter` date NOT NULL,
`petrol_filter` date NOT NULL,
`air_filter` date NOT NULL,
`cabin_filter` date NOT NULL,
`carbon_filter` date NOT NULL,
`front_breaks` date NOT NULL,
`rear_breaks` date NOT NULL,
`front_disc_breaks` date NOT NULL,
`rear_disc_breaks` date NOT NULL,
`break_fluids` date NOT NULL,
`gear_oil` date NOT NULL,
`gear_controller` date NOT NULL,
`gear_filter` date NOT NULL,
`tires` date NOT NULL,
`kteo` date NOT NULL,
`freon` date NOT NULL,
`freon_filter` date NOT NULL,
`steering_fluids` date NOT NULL,
`axle_oil` date NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Car records' AUTO_INCREMENT=1";
return $query;
}
private function createRecordsTable($database) {
$query = "CREATE TABLE IF NOT EXISTS $database.records (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`time` time NOT NULL,
`driver_id` bigint(20) NOT NULL,
`client_id` bigint(20) NOT NULL,
`car_id` bigint(20) NOT NULL,
`room_num` int(11) NOT NULL,
`departure` text NOT NULL,
`course` text NOT NULL,
`destination` text NOT NULL,
`arrives_from` text NOT NULL,
`arrival_info` text NOT NULL,
`route_type` bigint(20) NOT NULL,
`payment_method` int(11) NOT NULL,
`total_cost` float NOT NULL,
`expenses` float NOT NULL,
`profit` float NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`driver_id`) REFERENCES $database.drivers(`id`),
FOREIGN KEY (`client_id`) REFERENCES $database.clients(`id`),
FOREIGN KEY (`car_id`) REFERENCES $database.cars(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
return $query;
}