SQL syntax question

[wolf73]

I want to cretae a relationship between table test3 and test, what am I doing wrong here when I create a foreign key.


CREATE test3(

order_no BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
Order_Date date,
Customer_SID integer,
Amount double,
FOREIGN KEY Customer_SID REFERENCES test(id)
);

 

[rac2]

I suppose you really did type a comma after the datatype of the column. If so, that is a syntax error.

See

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Did you see an error message when you ran the CREATE TABLE statement? If so, posting that is helpful in solving the problem.

I think it might be good practice to give a different name to the foreign key, such as fk_Customer_SID . Instead of using the same name for the column and the foreign key. In general, I think it is good to use different names for different things.

HTH

 

[r937]

the error is that you forgot the TABLE keyword

it's not CREATE test3(

it should be CREATE TABLE test3(

also, the FK should go in parentheses

FOREIGN KEY (Customer_SID) REFERENCES test(id)

finally, you won't get the relationship to work unless you use TYPE=InnoDB




r937.com | rudy.ca

 

[dabruins]

You also need to create an index for the foreign key.

CREATE test3(

order_no BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
Order_Date date,
Customer_SID integer,
Amount double,
INDEX (Customer_SID),
FOREIGN KEY Customer_SID REFERENCES test(id)
);


Setting up FK's can be tedious!