A Foreign key referencing multiple unique key

Question

I have 3 tables:

class_a

     CREATE TABLE class_a (
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       std_id INT NOT NULL UNIQUE,
       name varchar(225) NOT NULL)

class_b

     CREATE TABLE class_b (
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       std_id INT NOT NULL UNIQUE,
       name varchar(225) NOT NULL)

sn_number

      CREATE TABLE sn_number (
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       pin INT NOT NULL UNIQUE,
       serial VARCHAR(255) NOT NULL UNIQUE,
       std_id INT NULL DEFAULT NULL,
       FOREIGN KEY(std_id) REFERENCES class_a(std_id)
       )

How can I reference unique std_id in class_a and class_b table as a foreign key in sn_number table.

I want to achieve something like ALTER TABLE sn_number ADD FOREIGN KEY(std_id) REFERENCES class_a(std_id), class_b(std_id)

I have tried doing this ALTER TABLE sn_number ADD FOREIGN KEY(std_id) REFERENCES class_a(std_id)

followed by

ALTER TABLE sn_number ADD FOREIGN KEY(std_id) REFERENCES class_b(std_id) on sn_number table but will keep overwriting each other.

I have read these: Foreign Key Referencing Multiple Tables and

Composite key as foreign key (sql) But I can't find the solution to the problem am having.

Having this problem is a hint to you that you have a mistake in your database design. A class is not a student. So you usually would not refererence a std_id by a class-table, but by a student table. And you should think about why you have two class tables, since you might get the same problem when you want to connect e.g. teachers or rooms to classes. Basically: There exists classes. There exists students. There exists sn_numbers. Have one table for each, and link them (if you want, you can literally draw them and connect them by lines). If you need specialisation, you can add them later. — Solarflare, Jul 10 '16 at 15:21
@ Abk Contrary to the comment by @Solarflare & another answer, your need for two foreign keys from the same table & column list is not a symptom of poor design. (Although yours is a poor design.) For the problem with your code see my answer. — philipxy, Jul 16 '16 at 04:05
@Abk Since I'm back here: as an amendment: I didn't ask about the meaning of sn_numbers. If they are exclusive to students, you can use a students table and add the pin/sn_number to that (it's basically a merger of two entities with a 1:1 relation). But since your std_id in sn_number can be null, I guess there can be sn_numbers for other reasons too (maybe unassigned id cards). Vice versa, you could refer from classes to that std_id-column in sn_numbers (treating the sn_numbers-table as a student_id-table). To do that would just have practical reasons (# of tables), no logical. — Solarflare, Jul 16 '16 at 08:01
Do you want the non-NULL std_id values in sn_number to be in *both* class_a & class_b? (See my answer.) Or *either* (exclusive-OR or inclusive-OR)?? (See my answer re "*reminiscent*.) Or do you want the std_id values in class_a & class_b to all be in sn_number?? (That means you want class_a `FK (std_id) REFERENCES sn_number (std_id)` and class_b `FK (std_id) REFERENCES sn_number (std_id)` with sn_number std_id `NOT NULL`.) PS Please show your DDL and the corresponding *error messages*. — philipxy, Jul 16 '16 at 08:33
Your FK needs are unclear. Please explain what your application is about. Please give for each base table a statement template parameterized by column names. (A table holds the rows that make that *predicate* into a true statement.) Eg for class_a it could be "student STD_ID with name NAME attends class ID". But it is *really not clear* what your application & predicates are (nor the consequent FDs, candidate keys, FKs & other constraints). — philipxy, Jul 16 '16 at 09:21

score 1 · Accepted Answer · answered Jul 10 '16 at 13:09

1

Foreign key must reference only one parent table. This is fundamental to both SQL syntax, and relational theory.

What you can do, is add another table classes or students that contain all std_id , then just reference the FK to it.

answered Jul 10 '16 at 13:09

sagi

40,026
6
59
84

No, you can declare as many constraints as you want. But only one per constraint name. – philipxy Jul 13 '16 at 21:32

philipxy · Answer 2 · 2016-07-13T22:09:47.923

0

Since you haven't explicitly given a constraint name in your FOREIGN KEY declarations, the DBMS makes one up from the table name sn_number. Your problem is that you are thus implicitly declaring the same constraint name each time, so the old info for the name is lost. Just use different explicit constraint names for different cases of table & column list REFERENCES table & column list.

CONSTRAINT fk_sn_number_a FOREIGN KEY(std_id) REFERENCES class_a(std_id)
CONSTRAINT fk_sn_number_b FOREIGN KEY(std_id) REFERENCES class_b(std_id)

Just learn about the basics of Using FOREIGN KEY Constraints.

PS As remarked in a comment, this is a poor design. But contrary to the comment & another answer, your need for two foreign keys from the same table & column list is not a symptom of poor design. But notice that the problems that people usually have with "Foreign Key Referencing Multiple Tables" in questionable designs is that they think that their tables as designed need a foreign key from one place to two places when they don't. Such a design doesn't even involve a foreign key, it just involves something reminiscent of a foreign key.

edited Jul 13 '16 at 22:09

answered Jul 13 '16 at 21:42

philipxy

14,867
6
39
83

Either you or me didn't get his tables - I understood the following: some students are in class a, some in class b, most of them are in sn_number, so he tried to create a reference to a complete list of students as a union of classes-tables. That's not how sql works, you cannot do this with foreign keys that way. Thus the very basic idea: a student id refers to a student table (maybe indirectly to a key candidate). There are reasons to use your construct (thus "hint" and not "this is always wrong"), but for beginners there aren't. That you can technically do it doesn't mean it makes sense. – Solarflare Jul 16 '16 at 08:26
I just added a comment to the question about various things Abk might have meant. Re "most of them are in sn_number" Don't you mean *all* of them? Re "student id refers to a student table" I agree that might be what they want, using sn_number as the "student table", but then the FKs go the other way and sn_number must be NOT NULL. Re "doesn't make sense" It does if nr_number ids identify lockers available for students in classes. – philipxy Jul 16 '16 at 08:52
My comment was mostly directed to him as an added explanation. I'm 120% certain you know how foreign keys work, I could probably have stopped after the first 8 words and you would have known what I wanted to say. Apart maybe from the "all" - I actually meant "most", in case the reference was meant in the direction he described (since sn_number might or might not be the missing student table). And I meant, it "doesn't make sense" to use all technical possible methods. He won't need dual foreign keys for the next 3 years and if he does, I'm pretty sure it could be implemented in another way. – Solarflare Jul 16 '16 at 17:03

A Foreign key referencing multiple unique key

2 Answers2