Contents
Can a table have two foreign keys referencing the same table?
You can add more than one foreign key constraint to a single column. For example, if you create the following tables: CREATE TABLE customers ( id INT PRIMARY KEY, name STRING, email STRING ); CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT UNIQUE, item_number INT );
Can a foreign key reference a column in the same table?
FOREIGN KEY constraints can reference another column in the same table, and is referred to as a self-reference. A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.
How to make multiple column foreign key in MySQL?
I have a table that has a primary key consisting of two columns (product_id, attribute_id). I have another table that needs to reference this table. How can I make a foreign key in the other table to link it to a row in the table with two primary keys?
Can a table have more than one foreign key?
There can only be one primary key on a table. The fact in can consist of more than one field does not increase number of primary keys, there’s still one. Since a part of the PK pair is not unique, you obviously have to create a foreign key that refers to two fields as well: REFERENCES t1 (f1, f2). Example:
Is it possible to reference fields other than the primary key?
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.
How to update a column in another table?
Basically, I need to update a column in one table based on a column’s value in another table. This question has been asked at least a couple times here and every answer with high votes states to join the two tables and set the value of a column in one table based on the value of the applicable column in the other table: