CODEWITHSUNDEEP
mysql
Eric Hung
Eric Hung

Reputation: 522

MySQL - How can I get a foreign key

I'm reading Head First SQL and is very confused about the use of foreign key. In chapter 7 it shows an example about creating new table with foreign key as following

CREATE TABLE interests(
    int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    interest VARCHAR(50) NOT NULL,
    CONSTRAINT my_contacts_contact_id_fk
    FOREIGN KEY (contact_id)
    REFERENCES my_contacts (contact_id)
);

If my memory serves me right, it creates table interests with a column contact_id which is the foreign key from my_contacts table. However, while I was reading chapter 8 the book asked us to pull out columns, profession for example, to create a new table for reference. For example,

        profession
prof_id (primary key) (parent key)
profession VARCHAR(20)

        my_contacts
contact_id (primary key)
prof_id (foreign key)

Where does my_contacts.prof_id come from? Originally my_contacts table had profession column and it somehow turned into profession_id by some kind of magic. Now, I have two tables

        profession
prof_id (primary key)
profession VARCHAR(20)

        my_contacts
contact_id (primary key)
profession

Do I need to map the profession.prof_id to my_contacts and then drop my_contacts.profession column so I can get a foreign key?

Upvotes: 0

Views: 41

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

I'm answering just to address what I believe to be the biggest oversight in your question, which regards what you said here:

it creates table interests with a column contact_id which is the foreign key from my_contacts table

Adding a foreign key constraint to a MySQL table does not create a column. Rather, you still need to create a column for a foreign key as you would any other column. So, your CREATE TABLE statement should look something like this:

CREATE TABLE interests (
    int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    interest VARCHAR(50) NOT NULL,
    contact_id INT,
    CONSTRAINT my_contacts_contact_id_fk FOREIGN KEY (contact_id)
        REFERENCES my_contacts (contact_id)
);

Upvotes: 1

Related Questions