Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    23

    Unanswered: mysql version 4.0.17

    I just found out that my version of mysql does not support control structure. Now I've notice that I can't create a foreign key. Is it my syntax or version 4.0.17 does not support foreign key?

    FOREIGN KEY(fk__ID)
    REFERENCES table1(pk__ID)
    ON UPDATE CASCADE
    ON DELETE NO ACTION,

    Thanks,

    xayavon

  2. #2
    Join Date
    Nov 2003
    Posts
    91
    Only the InnoDB table type supports referential integrity
    (and foreign keys). To create an InnoDB table:

    CREATE TABLE yourtablename (
    blah blah bitty blah,
    PRIMARY KEY(keyfield) )
    TYPE=INNODB;

    After creating the table, make sure it was created
    with the right table type by doing a,

    SHOW CREATE TABLE yourtablename;

    and look for the word INNODB.

  3. #3
    Join Date
    Jan 2004
    Posts
    15

    Post

    Hi

    MySQL Supoort Foreing Keys in InooDB Tables.

    For better understanding to read the articulate http://www.databasejournal.com/featu...le.php/2248101

    Tool to administer graphically the database
    http://www.mentattech.com/

    Kind Regards

  4. #4
    Join Date
    Dec 2003
    Posts
    23
    Thanks for the replies,

    CREATE TABLE IF NOT EXISTS activitycodes
    (
    pk_ActivityCode_ID smallint UNSIGNED AUTO_INCREMENT NOT NULL,
    PRIMARY KEY (pk_ActivityCode_ID),
    ) TYPE=INNODB;

    CREATE TABLE IF NOT EXISTS activities
    (
    pk_Activity_ID int UNSIGNED AUTO_INCREMENT NOT NULL,
    fk_ActivityCode_ID smallint NOT NULL,
    PRIMARY KEY (pk_Activity_ID),
    INDEX (fk_ActivityCode_ID),
    FOREIGN KEY (fk_ActivityCode_ID)
    REFERENCES activitycodes(pk_ActivityCode_ID)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
    ) TYPE=INNODB;

    I don't see what can be wrong with this. I keep getting an error.

  5. #5
    Join Date
    Nov 2003
    Posts
    91
    So what's the error message?

    You have two extra commas that shouldn't be there:

    PRIMARY KEY (pk_ActivityCode_ID),

    ON DELETE NO ACTION,

    You will have fewer problems in the long run if you change
    your primary keys from smallints and unsigned ints to just
    a plain 'int'.

  6. #6
    Join Date
    Dec 2003
    Posts
    23
    here is the error that I get.

    Error Code : 1005
    Can't create table './test/activities.frm' (errno: 150)
    (16 ms taken)

  7. #7
    Join Date
    Nov 2003
    Posts
    91
    C:\mysql\bin>perror 150
    Error code 150: Unknown error
    150 = Foreign key constraint is incorrectly formed



    I just ran the following successfully on my computer.
    (As I mentioned earlier, you will have fewer problems
    if you change the primary key to just an 'int'.)


    drop TABLE activitycodes;
    CREATE TABLE IF NOT EXISTS activitycodes
    (
    pk_ActivityCode_ID int AUTO_INCREMENT NOT NULL,
    PRIMARY KEY (pk_ActivityCode_ID)
    ) TYPE=INNODB;


    drop TABLE activities;
    CREATE TABLE IF NOT EXISTS activities
    (
    pk_Activity_ID int AUTO_INCREMENT NOT NULL,
    fk_ActivityCode_ID int NOT NULL,
    PRIMARY KEY (pk_Activity_ID),
    INDEX (fk_ActivityCode_ID),
    FOREIGN KEY (fk_ActivityCode_ID)
    REFERENCES activitycodes(pk_ActivityCode_ID)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    ) TYPE=INNODB;

  8. #8
    Join Date
    Dec 2003
    Posts
    23
    vanekl,

    Thanks for the solution! The problem was my primary key was defined int unsigned, but I defined my fk as just int. I will take your advice and change my pk definition.

    Thanks,

    xayavon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •