Professional Web Applications Themes

Foreign key - MySQL

Hi First, i assume i RTFM but am new to MySQL world so i got some problems. I got 2 tables TABLE1 T1Col1 Varchar(30) PRIMARY KEY TABLE2 T2col1 T2col2 What i'd like to do is to set the T2col2 as foreign key for the T1Col1. Any1 got the synthaxe to do it ? Regards Bruno...

  1. #1

    Default Foreign key

    Hi

    First, i assume i RTFM but am new to MySQL world so i got some problems.
    I got 2 tables

    TABLE1
    T1Col1 Varchar(30) PRIMARY KEY

    TABLE2
    T2col1
    T2col2



    What i'd like to do is to set the T2col2 as foreign key for the T1Col1.
    Any1 got the synthaxe to do it ?

    Regards
    Bruno


    Bruno Guerpillon Guest

  2. #2

    Default Re: Foreign key

    Bruno Guerpillon wrote:
    > Hi
    >
    > First, i assume i RTFM but am new to MySQL world so i got some problems.
    > I got 2 tables
    >
    > TABLE1
    > T1Col1 Varchar(30) PRIMARY KEY
    >
    > TABLE2
    > T2col1
    > T2col2
    >
    >
    >
    > What i'd like to do is to set the T2col2 as foreign key for the T1Col1.
    > Any1 got the synthaxe to do it ?
    >
    > Regards
    > Bruno
    >
    >
    To use foreign keys, you have to:
    - use InnoDB tables;
    - have a primary key in the parent table
    - have a key for the foreign key candidate column in the child table
    - use the constraint syntax:
    CONSTRAINT `constraint_name` FOREIGN KEY `key_name` (T2col2) references TABLE1 (T1Col1)

    This is the RTFM part :)
    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html[/url]

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Foreign key

    If you've installed MySQL locally, it comes with a pretty decent manual,
    especially if it's on Windows. you just go into the index tab and type in
    alter table. you can also learn about alter table by looking at the SQL
    generated when you modify a table in the query browser.
    I think the syntax would look something like
    ALTER TABLE TABLE2 ADD FOREIGN KEY ix_t2t2col2t1col1 (T2col2) REFERENCES
    TABLE1(T1Col1);

    "Bruno Guerpillon" <totototo.fr> wrote in message
    news:43d60d41$0$375$636a55cenews.free.fr...
    > Hi
    >
    > First, i assume i RTFM but am new to MySQL world so i got some problems.
    > I got 2 tables
    >
    > TABLE1
    > T1Col1 Varchar(30) PRIMARY KEY
    >
    > TABLE2
    > T2col1
    > T2col2
    >
    >
    >
    > What i'd like to do is to set the T2col2 as foreign key for the T1Col1.
    > Any1 got the synthaxe to do it ?
    >
    > Regards
    > Bruno
    >

    Jim Michaels Guest

  4. #4

    Default Foreign key

    I wish to alter the following code for mySQL:
    create table titles (
    isbn varchar (20) NOT NULL,
    title varchar (100) NOT NULL,
    editionNumber int NOT NULL,
    copyright varchar (4) NOT NULL,
    publisherID int NOT NULL,
    imageFile varchar (20) NOT NULL,
    price real NOT NULL,
    constraint fk_titles foreign key (publisherID)
    references publishers (publisherID),
    constraint pk_titles primary key (isbn)
    )
    ;

    I tried and got the following result:
    mysql> CREATE TABLE titles(
    -> isbn varchar(20) NOT NULL,
    -> title varchar(100) NOT NULL,
    -> editionNumber int NOT NULL,
    -> copyright varchar(4) NOT NULL,
    -> publisherID int NOT NULL,
    -> imageFile varchar(20) NOT NULL,
    -> price real NOT NULL,
    -> FOREIGN KEY (publisherID) REFERENCES publishers (publisherID) ON
    DELETE C
    ASCADE ON UPDATE CASCADE,
    -> PRIMARY KEY (isbn)
    -> );
    ERROR 1005 (HY000): Can't create table '.\books\titles.frm' (errno:
    150)

    Would be really thankful if somebody could help me here with the
    FOREIGN KEY line coz I guess that's where the problem is.

    Thanks
    Roohbir

    roohbir Guest

  5. #5

    Default Re: Foreign key

    My guess: You are using a 4.x series MySQL. Declare an index for your
    foreign key, like:

    ....
    INDEX idxPublisher(publisherID),
    constraint fk_titles foreign key(publisherID) references publishers
    (publisherID),
    ....

    This should solve the problem.


    roohbir wrote: 
    Dikkie Guest

  6. #6

    Default Re: Foreign key

    I am using MySQL 5.0 but tried your suggestion anyways. It didn't work.
    Any other ideas?
    Roohbir


    Dikkie Dik wrote: [/ref]

    roohbir Guest

  7. #7

    Default Re: Foreign key

    Just checking the obvious: does the publishers table exist, and does it
    have a publisherID field which is of type int?

    roohbir wrote: [/ref]
    >[/ref]
    Willem Guest

Similar Threads

  1. Serial Foreign Key
    By Jonathan Stafford in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 7th, 06:37 AM
  2. Find out if a key is foreign or not?
    By Troy Stark in forum ASP Database
    Replies: 1
    Last Post: September 3rd, 10:41 AM
  3. foreign keys
    By Sybrand Bakker in forum Microsoft Access
    Replies: 2
    Last Post: July 25th, 10:04 PM
  4. Foreign char - ASP to DB
    By Charles Katili in forum ASP Components
    Replies: 0
    Last Post: July 21st, 06:39 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139