CODEWITHSUNDEEP
mysql
Mariana
Mariana

Reputation: 11

How to specify a foreign key in mysql

I have created 3 tables in a music tracks database Track ( id, album, genre) Album( id, title) Contributed ( artist, track, role)

I have chosen Id as primary keys for the first table, ( I'd, album) for the second table.

Do I need a primary key in my third table? What is my foreign key? How do I specify my foreign key?What are the unique identifiers in my third table? I tried to populate it using tracks as foreign key( referenced to Track table, id column) but it gives me error. Can anyone help?

Upvotes: 0

Views: 71

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

For the third table, we can have two foreign keys:

  • Referencing to artist table (assuming we have an artist table with id and name).
  • Referencing to track table.

Syntax for foreign key should look like this (in create table script):

CONSTRAINT fk_contributed_track FOREIGN KEY
REFERENCS track(id)

Here is an example of a create table script with foreign key.

As far as primary key is concerned, it depends on business rules (i.e. whether one artist can have multiple roles for a track). However, for simple design, I would recommend having a numeric auto increment primary key.

Upvotes: 1

Related Questions