CODEWITHSUNDEEP
mysqlphpmyadmin
fhs14647
fhs14647

Reputation: 163

PHPmyadmin how to use double primary key for reference to 2 foreigen key with correct combinations

I have a problem with mysql via phpmyadmin. I have a table with teachers (tid, name) and with subjects (sid, name). Because this is a n:m cardinality, i made a third table called teacher_subjects with 2 primary keys (tid, sid).

This combination I want to use for a fourth table called booking (lid, date, tid, sid). How can I set a foreign key to the table teachers_subjects with correct combinations?

I managed to built up the tables including their relationships completely, but in the table booking I am also able to choose combinations that are not implemented in the table teacher_subject.

I think that has to work with multiple foreign keys, but I don`t know how to manage this.

Upvotes: 0

Views: 1602

Answers (1)

Frazz
Frazz

Reputation: 3043

Any table has maximum ONE primary key. A primary key can be defined by a set of more than one columns... you can call this a composite primary key, but please be precise and do not say the table has 2 primary keys.

That said, both primary keys and foreign keys can be composite (defined by more than one column).

In your case, you can do it when you create the table, like this:

CREATE TABLE teacher_subjects(
  tid  INTEGER,
  sid  INTEGER,
  PRIMARY KEY (tid, sid)
);
CREATE TABLE booking(
  lid INTEGER,
  date DATE,
  tid  INTEGER,
  sid  INTEGER,
  FOREIGN KEY (tid, sid) REFERENCES teacher_subjects (tid, sid)
);

...or you can do it afterwards, with an ALTER TABLE statement, like this:

CREATE TABLE teacher_subjects(
  tid  INTEGER,
  sid  INTEGER,
  PRIMARY KEY (tid, sid)
);
CREATE TABLE booking(
  lid INTEGER,
  date DATE,
  tid  INTEGER,
  sid  INTEGER
);
ALTER TABLE booking
  ADD FOREIGN KEY (tid, sid) REFERENCES teacher_subjects (tid, sid);

Upvotes: 1

Related Questions