Câu hỏi phỏng vấn MySQL
Câu hỏi

What is self referencing foreign key? Give an example.

Câu trả lời

A self-referencing foreign key is a type of foreign key in a database table that establishes a relationship with the primary key of the same table. This is also known as a recursive foreign key. The purpose of a self-referencing foreign key is typically to model hierarchical or recursive relationships within the same table, such as organizational structures or parent-child relationships.

Example of a Self-Referencing Foreign Key

Consider a table named Employee that includes information about employees in a company. Each employee has a unique employee number (EmpID), a name (EmpName), and a manager's employee number (MgrID). The MgrID in this table acts as a foreign key that references the EmpID of another employee within the same Employee table. This setup allows the table to express the reporting relationships between employees and their managers.

Here is how you might define such a table in SQL:

sql Copy
CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(255),
    MgrID INT,
    CONSTRAINT FK_Manager FOREIGN KEY (MgrID) REFERENCES Employee(EmpID)
);

In this example:

  • EmpID is the primary key of the table.
  • MgrID is the foreign key that references EmpID, establishing the self-referencing relationship.
  • Each row in the Employee table can have a MgrID that points to the EmpID of the manager. This mean...
junior

junior

Gợi ý câu hỏi phỏng vấn

expert

How do you make schema changes to a live database without downtime?

middle

What happens if a parent row which is referenced by child row is being deleted in case of foreign key constraint?

expert

What is difference between horizontal and vertical partitioning? Does MySQL support both horizontal
and vertical partitioning?

Bình luận

Chưa có bình luận nào

Chưa có bình luận nào