Help with Foreign Keys

[timbr8ks]

I am having trouble understanding how to get a foreign key to have the same value as the field in the foreign table. Example:

I have an Artist Table and a CD Table. Bill Evans has an ArtistID of 15 in my Artist Table, but the ArtistID in my CD Table is 84. This is because both are set to auto_increment, and when records are added, they increment differently. After all, they don't know any better. How do you get the database to recognize the ArtistID in the CD Table as a foreign key controlled by the Artist Table so that it has the right value? As it is, Artist.ArtistID and CD.ArtistID are not equal.

Many thanks.

[Anon]

Your foreign key should NOT be auto_increment. It should be the same data type as the primary key it is referencing. You should be handling the data in the foreign key.

• Justin

[[deleted]]

"How do you get the database to recognize the ArtistID in the CD Table as a foreign key controlled by the Artist Table so that it has the right value?"

By changing your table definition to add a foreign key. (This is not supported by MySQL whithout InnoD😎 Check the manual for the exact syntax.

Once a foreign key is created, it does not automatically enter the correct value (it doesn't know which one you mean ofcourse) but it will limit you to entering only values that exist in the table you are referencing. So you cannot enter an artistid that does not exist in the artist table.

[Anon]

Autoincrementing fields are meant to identify unique rows in a table. Since the second table you mentioned was for CDs, then its autoincement field should be used to identify each CD, not the artist on said CD.

So, let's say the artist table is something like:

create table artists (fullname text, id serial, yada text);

Then the CD table should be something like this:

create table artwork (fullname text, id serial, artistid int constraint artwork_owner references artists(id));

Now, when you insert into the artwork table, there has to already be an artist in the artist table with the id you are putting into artwork.artistid.

Further, if you try to delete the row from the artist table for an artist who has artwork listed in the artwork table, you will be told that there are records in the artwork table that refer to it and you have to delete those before you can delete that artist.