CODEWITHSUNDEEP
mysqlsqlforeign-keysfieldrecord
Charlie
Charlie

Reputation: 1

MySQL - How to create a foreign key between three tables

I have been set the task of creating 3 tables in MySql. One table called subjects with a field called subject_id as the primary key, another table called students with a field called student_id and a final table called entries.

The entries table must have two foreign keys, subject_id and student_id.

This is the official task:

image

Can anyone possibly help?

Upvotes: 0

Views: 2320

Answers (2)

Kevin
Kevin

Reputation: 781

Something like this

CREATE TABLE Subjects (
   SubjectId INT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (SubjectId))

CREATE TABLE Students (
   StudentId INT NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (StudentId))

CREATE TABLE Entries(
   EntriesId INT NOT NULL AUTO_INCREMENT,
   SubjectId INT NOT NULL,
   StudentId INT NOT NULL,
   FOREIGN KEY (SubjectId) REFERENCES Subjects (SubjectId),
   FOREIGN KEY (StudentId) REFERENCES Students (StudentId))

Upvotes: 0

Read Hughes
Read Hughes

Reputation: 53

In your create table query after you define the column that will be your foreign key, simply write "Foreign Key - References -" and specify the column (where I wrote the first dash mark) you wish to connect to the other table. The second dash should be the table name followed by the column it references in parentheses.

If the table is already made, just use an alter table query and write "add foreign key - references -" with the same format as above.

Upvotes: 1

Related Questions