Foreign key problems in mYSQL

Hello I am trying to get Foreign keys in the DB to work. My test code looks like the following :


CREATE TABLE customer (Customer_ID INT NOT NULL AUTO_INCREMENT,
Customer_Name varchar(20),
Customer_Address varchar(20),
Customer_Phone varchar(20),
Customer_Email varchar(20),

PRIMARY KEY (Customer_ID))
;

CREATE TABLE Agent (Agent_ID INT NOT NULL AUTO_INCREMENT,
Agent_Name varchar(20),
Agent_Address varchar(20),
Agent_Telephone varchar(20),
Agent_Email varchar(20),

PRIMARY KEY (Agent_ID))
;

CREATE TABLE Assign (Assign_ID INT NOT NULL AUTO_INCREMENT,
Customer_ID INT,
Agent_ID INT,

PRIMARY KEY (Assign_ID),

FOREIGN KEY(Customer_ID) REFERENCES Customer
ON UPDATE CASCADE,

FOREIGN KEY (Agent_ID) REFERENCES Agent
ON UPDATE CASCADE

)

;


I did the following to access the database
mysql -u root -p testDB
DESCRIBE Assign

It shows in the second column Assign_ID is a PRIMARY KEY but Customer_ID and Agent_ID not as FOREIGN KEYS.

Quesiton

1) Why doesn't MYSQL allow me to create FOREIGN KEY's ?
2) If I am doing something wrong can someone type out what I am doing wrong in MYSQL?


Looking forwward to your feedback.

dannyh

n9905381@scholar.nepean.uws.edu.au

Um, you've hit what I would consider to be one of MySQL's greatest flaws. It doesn't support foreign keys. At all. It'll accept a SQL DDL statement containing them, but will completely ignore what they actually mean -- as 3.22 they're not even stored, though there are indications that that will change so that at least a dump of the DB will give the right results.

Unfortunately, there isn't really any way to work around this. You could maybe implement some of your own referential validation logic by building transactions up for every DML statement, but that would get clunky and slow (not to mention be prone to the same logic errors as you're likely to find in any app logic). The best way forward really is just to make sure that your tables are carefully designed and that you don't need to rely on cascading updates etc. A long way off perfect, but it's the best you'll get...

I've not looked at the PostGRES offering for this, but that may well support referential constraints so you may want to check that out.

PostgreSQL also does not yet have Foreign Key support, but they are working on it.

- Thank you for your prompt Response

- In regards to your typed answer

"The best way forward really is just to make sure that your tables are carefully designed and that you don't need to rely on cascading updates etc."

- Assuming the problem is : -
The create customer, asgent and assign.

How would I code the SQL statement's to accomplish the above statement you typed in the prevous question?

Question

1) The lack of support for
FOREIGN KEYS that MySQL is not ANSI SQL complaint?


3) I am also trying to get a attribute with Money (eg $300.00) from looking at the datatypes in this DBMS I am not able to
create this. Is their some other datatypes I should be using for money?

Looking forward to your feedback.

dannyh
n9905381@scholar.nepean.uws.edu.au

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by christucker2:
Um, you've hit what I would consider to be one of MySQL's greatest flaws. It doesn't support foreign keys. At all. It'll accept a SQL DDL statement containing them, but will completely ignore what they actually mean -- as 3.22 they're not even stored, though there are indications that that will change so that at least a dump of the DB will give the right results.

Unfortunately, there isn't really any way to work around this. You could maybe implement some of your own referential validation logic by building transactions up for every DML statement, but that would get clunky and slow (not to mention be prone to the same logic errors as you're likely to find in any app logic). The best way forward really is just to make sure that your tables are carefully designed and that you don't need to rely on cascading updates etc. A long way off perfect, but it's the best you'll get...

I've not looked at the PostGRES offering for this, but that may well support referential constraints so you may want to check that out.[/quote]