#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    6
    Rep Power
    0
    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    81
    Rep Power
    15
    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.
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    PostgreSQL also does not yet have Foreign Key support, but they are working on it.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Posts
    6
    Rep Power
    0
    - 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]

Similar Threads

  1. MySQL password problems NOT root
    By braveheart in forum MySQL Help
    Replies: 2
    Last Post: February 15th, 2004, 08:18 AM
  2. Entering foreign key data using drop down menus
    By WillyWoo in forum PHP Development
    Replies: 0
    Last Post: January 31st, 2004, 06:14 AM
  3. problems with unicode (UTF8) and MySQL 4.1
    By xdummy in forum MySQL Help
    Replies: 0
    Last Post: January 28th, 2004, 04:21 AM
  4. foreign key and relational data on simple tables
    By toocan in forum MySQL Help
    Replies: 7
    Last Post: January 22nd, 2004, 07:47 AM
  5. MySQL Control Center connection problems
    By FragMaster B in forum MySQL Help
    Replies: 3
    Last Post: January 19th, 2004, 09:44 AM

IMN logo majestic logo threadwatch logo seochat tools logo