I have a weird situation...I've got a sql query that is working on my production server, but not on the development server...here is the query in question:
CREATE TABLE IF NOT EXISTS `sizes` (
`size` varchar(255) NOT NULL,
`id` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `sizes` (`size`, `id`) VALUES
('Small', 3),
('Medium', 3),
('Large', 3),
('Medium', 5),
('Large', 5),
('Large', 6),
('Extra Large', 6),
('Extra Large', 4);
ALTER TABLE `sizes`
ADD KEY `id` (`id`);
ALTER TABLE `sizes`
ADD CONSTRAINT `sizes_fk_1` FOREIGN KEY (`id`) REFERENCES `products` (`id`);
As you can see, I'm just trying to create a simple table that holds the sizes of products I have. I have two columns...the actual size and also a foreign key id that references my products table, and as you can imagine, references the id of the product the size goes to. The problem is on my production server, the query is executed perfectly and the relationship is established. But on my development server, I get this error message when I fire off the command:
Can't create table `thecovh1_lucy`.`#sql-1eed4_17bf` (errno: 150 "Foreign key constraint is incorrectly formed")
Two things are confusing me here:
1. Why is it saying I'm trying to create a table named #sql-1eed4_17bf
And more importantly
2. What is wrong with the way I establish foreign keys?
For reference, I also tried this way of creating the table (without inserting data into the table, this was just trying to create the table):
CREATE TABLE IF NOT EXISTS sizes (
size VARCHAR(255) NOT NULL ,
id INT(10) NOT NULL ,
FOREIGN KEY (id) REFERENCES products(id));
Which is a way I created my uiid table (again, to success), but that is also giving me the exact same error message.
