Multiple Foreign Keys to a Single Column

Is it possible to reference one column as multiple foreign keys?

No.

That is, you cannot create a foreign key constraint this way. You can however, use a foreign key without a foreign key constraint.

All a foreign key is, is the value of another table's (or another record in the same table) primary key, which can be used in joins. In fact, you could reference fields other than the primary key, if all you need is to use the value for joins.

However, a foreign key constraint tells the database to enforce the rule that for every foreign key value in a table, the referenced table has a record with that as it's primary key. Enforcing that every foreign key in the PDF table had a primary key IN ALL FOUR TABLES won't work for you. So go ahead and use the field to reference other records, but simply do not create any foreign key constraint.

Multiple foreign keys to a single column

No, you can't have a single field as a foreign key to two different tables. How would you tell where to look for the key?

You would at least need a field that tells what kind of user it is, or two separate foreign keys.

You could also put the information that is common for all users in one table and have separate tables for the information that is specific for the user types, so that you have a single table with user id as primary key.

Using the same column in multiple foreign keys

you can't change to lectureids 2 because none exists in the first place in the table

And the cascade works the other way

Further GROUPS os a reserved word in mysql, and should so be avoided.

Last i had to remove the second foreign key, as it already exists in groups and is so unnecessary, as the fpoeign key in groups in groups already chekcs if such a lectureid exists

EXAMPLE for the working of foreign keys

CREATE TABLE lectures (
  lectureId INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId)
 );

✓

CREATE TABLE `groups` (
  lectureId INT NOT NULL,
  groupNo INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId,groupNo),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

✓

CREATE TABLE studentListed (
  studentId INT NOT NULL,
  lectureId INT NOT NULL,
  groupNo INT NULL,
  PRIMARY KEY (studentId,lectureId),
  #FOREIGN KEY (lectureId) REFERENCES lectures (lectureId) 
  #  ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (lectureId,groupNo) REFERENCES `groups` (lectureId,groupNo)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

✓

CREATE TRIGGER GroupDelete BEFORE DELETE ON `groups`
FOR EACH ROW
  UPDATE studentListed SET studentListed.groupNo = NULL
    WHERE studentListed.lectureId = OLD.lectureId
    AND studentListed.groupNo = OLD.groupNo;

✓

INSERT INTO lectures
VALUES
(1, "lecture1");

✓

INSERT INTO `groups`
VALUES
(1, 1, "group1");

✓

INSERT INTO studentListed
VALUES
(1, 1, 1);

✓

UPDATE lectures SET lectureId = 2 WHERE lectureId = 1

✓

SELECT * FROM studentListed

studentId | lectureId | groupNo
--------: | --------: | ------:
        1 |         2 |       1

UPDATE lectures SET lectureId=2 WHERE lectureId=1; /* Offending line */

✓

✓

db<>fiddle here

yu can't build that in t your way you have to break up the primary key from Groups and reference only groups with it, like

CREATE TABLE lectures (
  lectureId INT NOT NULL,
  title VARCHAR(10) NOT NULL,
  PRIMARY KEY (lectureId)
 );

✓

CREATE TABLE `groups` (
  lectureId INT NOT NULL,
  groupNo INT NOT NULL PRIMARY KEY,
  title VARCHAR(10) NOT NULL,
  UNIQUE KEY (lectureId,groupNo),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

✓

CREATE TABLE studentListed (
  studentId INT NOT NULL,
  lectureId INT NOT NULL,
  groupNo INT NULL,
  PRIMARY KEY (studentId,lectureId),
  FOREIGN KEY (lectureId) REFERENCES lectures (lectureId) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (groupNo) REFERENCES `groups` (groupNo)
    ON UPDATE CASCADE ON DELETE CASCADE
 );

✓

CREATE TRIGGER GroupDelete BEFORE DELETE ON `groups`
FOR EACH ROW
  UPDATE studentListed SET studentListed.groupNo = NULL
    WHERE studentListed.lectureId = OLD.lectureId
    AND studentListed.groupNo = OLD.groupNo;

✓

INSERT INTO lectures
VALUES
(1, "lecture1");

✓

INSERT INTO `groups`
VALUES
(1, 1, "group1");

✓

INSERT INTO studentListed
VALUES
(1, 1, 1);

✓

UPDATE lectures SET lectureId = 2 WHERE lectureId = 1

✓

SELECT * FROM studentListed

studentId | lectureId | groupNo
--------: | --------: | ------:
        1 |         2 |       1

UPDATE lectures SET lectureId=2 WHERE lectureId=1; /* Offending line */

✓

✓

DELETE FROM lectures WHERE lectureID = 2

✓

SELECT * FROM studentListed

studentId | lectureId | groupNo
--------: | --------: | ------:

db<>fiddle here

this will need still more improvment as your concept egts more complicated

Create a view with multiple foreign key referencing a single field

This is a common reporting pattern wherever the database architect has employed the "one true lookup table" model. I'm not going to get bogged down in the merits of that design. People like Celko and Phil Factor are far more erudite than me at commenting on these things. All I'll say is that having reported off over sixty enterprise databases in the last 15 years, that design is pervasive. Rightly or wrongly, you're probably going to see it over and over again.

There is currently insufficient information to definitively answer your question. The answer below makes assumptions on what I think is the most likely missing information is.

1. I'll assume your product table is named PRODUCT
2. I'll assume your all-powerful lookup table is call REFS
3. I'll assume RefCodeKey in REFS has a unique constraint on it, or it is the a primary key
4. I'll assume the REFS table is relatively small (say < 100,000 rows). I'll come back to this point later.

5. I'll assume that the foreign keys in the PRODUCT table are nullable. This affects whether we INNER JOIN or LEFT JOIN.

   SELECT prod.PC
          ,prod.PN
          ,reg_code.label  as RCKey
          ,prod_stat.label as PSKey
          ,prod_clas.label as PCKey
   FROM   PRODUCT             prod
          LEFT JOIN REFS  reg_code ON prod.RCKey = reg_code.RefCodeKey
          LEFT JOIN REFS prod_stat ON prod.PSKey = prod_stat.RefCodeKey
          LEFT JOIN REFS prod_clas ON prod.PCKey = prod_clas.RefCodeKey
   ;
   

The trick is that you can refer to the REFS table as many times as you like. You just need to give it a different alias and join it to the relevant FK each time. For example reg_code is an alias. Give your aliases meaningful names to keep your code readable.

Note: Those RCKey/PSKey/PCKey names are really not good names. They'll come back to bite you. They don't represent the key. They represent a description of the thing in question. If it's a region code, call it region_code

The reason I'm assuming the REFS table is relatively small, is that if it's really large (I've seen one with 6 million lookup values across hundreds of codesets) and indexed to take RefCodeType into consideration, you might get better performance by adding a filter for RefCodeType to each of your LEFT JOINs. For example:

       LEFT JOIN REFS prod_clas ON prod.PCKey = prod_clas.RefCodeKey
                                   AND prod_clas.RefCodeType = 'ProductClassificationKey' 

Two Foreign Keys on one column

No. You cannot have multiple foreign keys on the same column. From the MySQL documentation:

MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, “child table records” really refers to dependent records within the same table.

The reason for this is that MySQL won't be able to differentiate between the parents. Laravel (or any other framework) does not provide a work around for this issue.

This question as been asked in slightly different forms before. Example: it is possible to reference one column as multiple foreign keys

Multiple foreign keys referencing same table column in SQLAlchemy

The assumption is that in your scenario a user can rate other users, and they (themselves) can be rated. Basically, there is one table called User referencing other users within an application.

A relationship in which instances of a class are linked to other instances of the same class is called self-referential relationship, and that is exactly what you have here.

Here is a diagram that represents this self-referential many-to-many relationship that keeps track of ratings:

The Ratings table is the association table of the relationship. The foreign keys in this table are pointing at entries in the User table since it is linking users to users.

To add this table to your database, this is how you can go about it:

ratings = db.Table('ratings'
                   db.Column('my_ratings_id', db.Integer, db.ForeignKey('user.id'))
                   db.Column('other_people_rating_id', db.Integet, db.ForeignKey('user.id'))
                   )

This is an auxiliary table (directly-translated as seen above) that has no data other than foreign keys. It is, therefore, created without an associated model class.

To declare the many-to-many relationship in the User table, add this:

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), unique=True, nullable=False)
    
    
    def __repr__(self):
        return f'{self.username}'
        
    my_ratings = db.relationship(
                                 'User',
                                 secondary=ratings,
                                 primaryjoin=(ratings.c.my_ratings_id == id),
                                 secondaryjoin=(ratings.c.other_people_rating_id == id),
                                 backref = db.backref('other_people_rating', lazy='dynamic'), lazy='dynamic'
                                 )

I am defining the relationship as seen from the left side user with the name my_ratings because when you query this relationship from the left side, you will get a list of all those on the right side. Visually, this is what I mean:

Examining all the arguments of the db.relationship() call, you will see that:

• User is the right side entity of the relationship.
• secondary configures the ratings association table
• primaryjoin indicates the condition that links the left side entity with the association table. The user id should match my_ratings_id
• secondaryjoin indicates the condition that links the right side entity with the association table. Again, other_people_rating_id should match the user id
• backref defines how this relationship will be accessed from the right side entity. From the left side, the relationship is named my_ratings, so from the right side, I decided to name it other_people_rating to represent all the left side users that are linked to the target user in the right side.
• The dynamic mode is used to set up the query to not run until specifically requested.
• The second lazy parameter applies to the left side query instead of the right side.

One foreign key with multiple columns VS multiple foreign keys with single column

My question: are SQL code 1 and SQL code 2 equal?

Not, they are not equivalent.

By the definition

Foreign Key Constraints

A foreign key constraint (also called a referential integrity
constraint) designates a column as the foreign key and establishes a
relationship between that foreign key and a specified primary or
unique key, called the referenced key. A composite foreign key
designates a combination of columns as the foreign key.

---

According to the above this declaration:

CONSTRAINT FK_E
      FOREIGN KEY (E1, E2, E3)
      REFERENCES F (E1, E2, E3),

assumes that there is either a primary key or unique constraint created on F table

CREATE TABLE F(
  .....
  .....
  CONSTRAINT my_pk PRIMARY KEY(E1, E2, E3)
)

---

while this declaration

 CONSTRAINT FK_E1
      FOREIGN KEY (E1)
      REFERENCES F (E1),

 CONSTRAINT FK_E2
      FOREIGN KEY (E2)
      REFERENCES F (E2),

 CONSTRAINT FK_E3
      FOREIGN KEY (E3)
      REFERENCES F (E3),

is in a need of existence of three constraints, either primary key or unique indexses/constraints:

CREATE TABLE F(
  .....
  .....
  CONSTRAINT my_pk1 PRIMARY KEY(E1),
  CONSTRAINT my_uq2 UNIQUE(E2),
  CONSTRAINT my_uq3 UNIQUE(E3)
)

---

Note 1- the table can only have one primary key, so only one constraint in your example could be the primary key, the rest 2 (or all 3) must be unique keys.

Note 2 - there is a slight semantic difference between the primary key constraint and the unique key constraint. The primary key values must be unique and must not contain null values while the unique key values can be NULL.

---

In the first case the table F can contain these values

E1  E2  E3
 1   1   1
 1   1   2
 2   2   1

and the child table can contain only these records:

E1  E2  E3
 1   1   1
 1   1   2
 2   2   1

but you cannot insert to the child table these combination of values because they don't exists in the parent table:

 E1  E2  E3
 1   2   1
 2   2   2

---

In the secondcase the table F can contain these values

E1  E2  E3
 1   1   1
 2   2   2
 3   3   3

but cannot contain these values, because each column must be unique:

E1  E2  E3
 1   1   1
 1   1   2
 2   1   3

while the child table can contain these records:

E1  E2  E3
 1   2   3
 3   1   2
 2   1   3

Are multiple foreign keys in a single field possible?

What you typically do is set up a many to many relationship with an intermediate linking table. Some thing like the following:

CREATE TABLE product (
  `id` integer AUTO_INCREMENT NOT NULL,
  -- other cols --
  PRIMARY KEY (`id`)
);

CREATE TABLE certification (
  `id` integer AUTO_INCREMENT NOT NULL,
  -- other cols --
  PRIMARY KEY (`id`)
);

CREATE TABLE product_certification (
   `product_id` integer NOT NULL,
   `certification_id` integer NOT NULL,
   PRIMARY KEY (`product_id`, `certification_id`),
   CONSTRAINT `product_id_product_id` 
     FOREIGN KEY (`product_id`) 
     REFERENCES `product` (`id`) ON DELETE CASCADE,
   CONSTRAINT `certification_id_certification_id` 
     FOREIGN KEY (`certification_id`) 
     REFERENCES `certification` (`id`) ON DELETE CASCADE
);

---

Related Topics