Foreign key not working in table

[pizzaluver13]

mysql> CREATE TABLE LOCATION

    -> (LOC_ROW INT(2) NOT NULL,

    -> LOC_COLUMN CHAR(1) NOT NULL);

Query OK, 0 rows affected (0.05 sec)

 

mysql> CREATE TABLE SHOE

    -> (SHOE_UPC INT(18) PRIMARY KEY,

    -> SHOE_COLOR CHAR( ,

    -> SHOE_TYPE CHAR(12),

    -> SHOE_MATERIAL VARCHAR(16),

    -> DESIGN_ID INT(3),

    -> LOC_ROW INT(2),

    -> LOC_COLUMN CHAR(1),

    -> FOREIGN KEY (DESIGN_ID) REFERENCES DESIGNER (DESIGN_ID),

    -> FOREIGN KEY (LOC_ROW) REFERENCES LOCATION (LOC_ROW),

    -> FOREIGN KEY (LOC_COLUMN) REFERENCES LOCATION (LOC_COLUMN));

ERROR 1005 (HY000): Can't create table './easad/SHOE.frm' (errno: 150)

[pizzaluver13]

that was SHOE_COLOR CHAR (*

 

[pizzaluver13]

ughhhhhhhh..

CHAR(

[pizzaluver13]

can anyone help????????????

[mikosiko]

in your table LOCATION...

You are declaring the columns LOC_ROW and LOC_COLUMN has NOT NULL , however in your table SHOE doesn't have the same definition.... make them equals.

[pizzaluver13]

Even if I do that it still doesn't work

 

[mikosiko]

Did you check that both INT columns in both tables have exactly the same sign also?

 

also... are both tables are using the Innodb engine?

 

are the columns indexed ?

 

you can also use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

 

here also you can read all the neccesary to detect any condition that could be causing that error.

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

[Pikachu2000]

Users will not mark their post as being "URGENT" by either making the post ALL CAPS or adding any hint that they need it done Quick or ASAP. All posts are treated equal' date=' your post is no more important than any other user's post on this forum. If it is "URGENT" pay someone to do it.[/quote']

[pizzaluver13]

 

I am using InnoDB

When I just use one foreign key it works (the designer one) but when i put the LOCATION table foreign keys it doesn't work

The location table was working fine too.

mysql> DESCRIBE LOCATION;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| LOC_ROW    | int(2)  | YES  |     | NULL    |       | 
| LOC_COLUMN | char(1) | YES  |     | NULL    |       | 
+------------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)


mysql> CREATE TABLE SHOE
    -> (SHOE_UPC INT(18) PRIMARY KEY,
    -> SHOE_COLOR CHAR(,
    -> SHOE_TYPE CHAR(12),
    -> SHOE_MATERIAL VARCHAR(16),
    -> LOC_ROW INT(2),
    -> LOC_COLUMN CHAR(1),
    -> DESIGN_ID INT(3),
    -> FOREIGN KEY (DESIGN_ID) REFERENCES DESIGNER (DESIGN_ID)
    -> FOREIGN KEY (LOC_ROW) REFERENCES LOCATION (LOC_ROW)
    -> FOREIGN KEY (LOC_COLUMN) REFERENCES LOCATION (LOC_COLUMN));
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 'FOREIGN KEY (LOC_ROW) REFERENCES LOCATION (LOC_ROW)
FOREIGN KEY (LOC_COLUMN) REF' at line 10

When i put 3 foreign keys it doesn't work.

[pizzaluver13]

Sorry, I forgot to put the comma in but still it gets me errors...

Okay it has something to do with LOCATION probably....

mysql> CREATE TABLE SHOE
    -> (SHOE_UPC INT(18) PRIMARY KEY,
    -> SHOE_COLOR CHAR(,
    -> SHOE_TYPE CHAR(12),
    -> SHOE_MATERIAL VARCHAR(16),
    -> LOC_ROW INT(2),
    -> LOC_COLUMN CHAR(1),
    -> DESIGN_ID INT(3),
    -> FOREIGN KEY (LOC_ROW) REFERENCES LOCATION (LOC_ROW),
    -> FOREIGN KEY (LOC_COLUMN) REFERENCES LOCATION (LOC_COLUMN));
ERROR 1005 (HY000): Can't create table './easad/SHOE.frm' (errno: 150)

[mikosiko]

I bet that you didn't read this question in my last post:

are the columns indexed ?

  (table location)

 

I don't see in the CREATE TABLE LOCATION that was originally posted any INDEX definition.

 

please read:

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

 

 

[pizzaluver13]

You mean like this??

 

mysql> CREATE TABLE SHOE
    -> (SHOE_UPC INT(18) PRIMARY KEY,
    -> SHOE_COLOR CHAR(,
    -> SHOE_TYPE CHAR(12),
    -> SHOE_MATERIAL VARCHAR(16),
    -> LOC_ROW INT(2),
    -> LOC_COLUMN CHAR(1),
    -> DESIGN_ID INT(3),
    -> INDEX (LOC_ROW),
    -> FOREIGN KEY (LOC_ROW) REFERENCES LOCATION (LOC_ROW),
    -> INDEX (LOC_COLUMN),
    -> FOREIGN KEY (LOC_COLUMN) REFERENCES LOCATION (LOC_COLUMN),
    -> FOREIGN KEY (DESIGN_ID) REFERENCES DESIGNER (DESIGN_ID));
ERROR 1005 (HY000): Can't create table './easad/SHOE.frm' (errno: 150)

[mikosiko]

NO!!  you need indexes in your LOCATION TABLE  one index for LOC_ROW column and one index for LOC_COLUMN column... and one more time:

read

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html