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.

On Jun 17, 6:22 pm, TheKeith <ksd...@gmail.com> wrote:
> 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.

Have a read through this:

http://dev.mysql.com/doc/refman/5.0/...nstraints.html

> Have a read through this:
>
> http://dev.mysql.com/doc/refman/5.0/...ey-constraints...



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.

On 18 Jun, 15:50, TheKeith <ksd...@gmail.com> wrote:
> > Have a read through this:
>
> >http://dev.mysql.com/doc/refman/5.0/...ey-constraints...
>
> 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.

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.

On Jun 18, 11:23 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 18 Jun, 15:50, TheKeith <ksd...@gmail.com> wrote:
>
>
>
> > > Have a read through this:
>
> > >http://dev.mysql.com/doc/refman/5.0/...ey-constraints...
>
> > 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.
>
> 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.


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.

On Jun 18, 12:12 pm, TheKeith <ksd...@gmail.com> wrote:
> On Jun 18, 11:23 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 18 Jun, 15:50, TheKeith <ksd...@gmail.com> wrote:
>
> > > > Have a read through this:
>
> > > >http://dev.mysql.com/doc/refman/5.0/...ey-constraints...
>
> > > 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.
>
> > 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.
>
> 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.



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

== Quote from TheKeith (ksdump@gmail.com)'s article
> On Jun 18, 12:12 pm, TheKeith <ksd...@gmail.com> wrote:
> > On Jun 18, 11:23 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> >
> >
> >
> > > On 18 Jun, 15:50, TheKeith <ksd...@gmail.com> wrote:
> >
> > > > > Have a read through this:
> >
> > > > >http://dev.mysql.com/doc/refman/5.0/...ey-constraints...
> >
> > > > 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.
> >
> > > 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.
> >
> > 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.
> AHH! -- I got it working finally. I'm not sure what I did right this
> time though.

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