Professional Web Applications Themes

newbie question--1 table's columns to link to other tables' primary keys - MySQL

I'm new to databases, so if my question doesn't make any sense, don't hold it against me. I have three tables--one stores user information (username, password, email, etc), another stores a list of songs--this table uses a 2-column primary key. One of the primary key columns is of DATETIME type and the other is a SMALLINT AUTO_INCREMENT type. My third table stores information about each listen--by that I mean a new record is made everytime a user listens to a song, and in that record, is the user and the song, so naturally there would only be two columns in ...

  1. #1

    Default newbie question--1 table's columns to link to other tables' primary keys

    I'm new to databases, so if my question doesn't make any sense, don't
    hold it against me. I have three tables--one stores user information
    (username, password, email, etc), another stores a list of songs--this
    table uses a 2-column primary key. One of the primary key columns is
    of DATETIME type and the other is a SMALLINT AUTO_INCREMENT type. My
    third table stores information about each listen--by that I mean a new
    record is made everytime a user listens to a song, and in that record,
    is the user and the song, so naturally there would only be two columns
    in this table, namely User (corresponding to only those users in the
    user table) and Song (corresponding to the primary key columns in the
    songs table).

    My question is this. How can I link the two columns in my "listens"
    table to the primary keys of the other two table, so that when and if
    a user should be deleted from teh "user" table, all listens by that
    user in the "listens" table would get deleted; also if any song should
    get deleted from the "songs" table, all listens of that song by any
    user, would get deleted from the "listens" table?

    BTW, I'm using mysql 5 on godaddy web hosting.

    Did this make sense? I'm not sure if I need a foreign key or what, and
    if so, how do I set that up? Help would be greatly appreciated--thanks.

    TheKeith Guest

  2. #2

    Default Re: newbie question--1 table's columns to link to other tables' primary keys

    On Jun 17, 6:22 pm, TheKeith <com> wrote: 

    Have a read through this:

    http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

    strawberry Guest

  3. #3

    Default Re: newbie question--1 table's columns to link to other tables' primary keys

     



    Hey thanks! So I've followed all the instructions, but for some reason
    I'm getting a:

    ERROR 1005 (HY000): Can't create table './keithsdb/
    kmfnlistens.frm' (errno: 150)

    when creating the referencing table, with this syntax:

    create table kmfnListens (listener varchar(20) not null, kmfnSongID
    bigint not null auto_increment, index (kmfnSongID), foreign key
    (kmfnSongID) references kmfnSongs(songID) on delete cascade on update
    cascade) engine=innodb;

    I tried creating the table without the foreign key clause, and it
    works. But I can't get the foreign key to work. After creating the
    table without it, I issued a:

    alter table kmfnListens foreign key kmfnSongID references
    kmfnSongs(songID) on delete cascade on update cascade;

    but got an error 1064 syntax error--what am I doing wrong? The
    referenced table.column is kmfnSongs.songID and that column is its
    primary key, so I'm stumped! Help! thanks.

    TheKeith Guest

  4. #4

    Default Re: newbie question--1 table's columns to link to other tables' primary keys

    On 18 Jun, 15:50, TheKeith <com> wrote: 

    >
    > Hey thanks! So I've followed all the instructions, but for some reason
    > I'm getting a:
    >
    > ERROR 1005 (HY000): Can't create table './keithsdb/
    > kmfnlistens.frm' (errno: 150)
    >
    > when creating the referencing table, with this syntax:
    >
    > create table kmfnListens (listener varchar(20) not null, kmfnSongID
    > bigint not null auto_increment, index (kmfnSongID), foreign key
    > (kmfnSongID) references kmfnSongs(songID) on delete cascade on update
    > cascade) engine=innodb;
    >
    > I tried creating the table without the foreign key clause, and it
    > works. But I can't get the foreign key to work. After creating the
    > table without it, I issued a:
    >
    > alter table kmfnListens foreign key kmfnSongID references
    > kmfnSongs(songID) on delete cascade on update cascade;
    >
    > but got an error 1064 syntax error--what am I doing wrong? The
    > referenced table.column is kmfnSongs.songID and that column is its
    > primary key, so I'm stumped! Help! thanks.[/ref]

    According to your first post:
    "One of the primary key columns is of DATETIME type and the other is a
    SMALLINT AUTO_INCREMENT type"
    But in the above you have "kmfnSongID bigint not null auto_increment".

    The joining fields must be identical.

    Captain Guest

  5. #5

    Default Re: newbie question--1 table's columns to link to other tables' primary keys

    On Jun 18, 11:23 am, Captain Paralytic <com> wrote: [/ref]
    > [/ref]







    >
    > According to your first post:
    > "One of the primary key columns is of DATETIME type and the other is a
    > SMALLINT AUTO_INCREMENT type"
    > But in the above you have "kmfnSongID bigint not null auto_increment".
    >
    > The joining fields must be identical.[/ref]


    Ah yeah, I neglected to mention that I changed teh structure of the
    referenced tables so that each one only has one primary key column--
    because I wasn't able to convert that table to innoDB as it was with
    the two-column primary key, one of which was auto-increment.

    TheKeith Guest

  6. #6

    Default Re: newbie question--1 table's columns to link to other tables' primary keys

    On Jun 18, 12:12 pm, TheKeith <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Ah yeah, I neglected to mention that I changed teh structure of the
    > referenced tables so that each one only has one primary key column--
    > because I wasn't able to convert that table to innoDB as it was with
    > the two-column primary key, one of which was auto-increment.[/ref]



    AHH! -- I got it working finally. I'm not sure what I did right this
    time though.

    TheKeith Guest

  7. #7

    Default Re: newbie question--1 table's columns to link to other tables' primary keys

    == Quote from TheKeith (com)'s article 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > >
    > > Ah yeah, I neglected to mention that I changed teh structure of the
    > > referenced tables so that each one only has one primary key column--
    > > because I wasn't able to convert that table to innoDB as it was with
    > > the two-column primary key, one of which was auto-increment.[/ref]
    > AHH! -- I got it working finally. I'm not sure what I did right this
    > time though.[/ref]

    from the error code you gave, it appears that the key constraint (foreign key) was
    incorrectly formed just like captain p mentioned if they are not of the same kind,
    it won't work.
    --
    POST BY: lark with PHP News Reader
    lark Guest

Similar Threads

  1. Foreign key constraint against primary keys in two different table.
    By Bill in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: August 19th, 03:28 PM
  2. Primary Keys
    By John Simmons in forum PHP Development
    Replies: 6
    Last Post: August 17th, 10:13 PM
  3. Replies: 12
    Last Post: August 3rd, 01:50 AM

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