How to implement multiple inheritance in SQL?

Question

In a relational database, if there is a Student table and Employee table, and a third table StudentAsst who indicates a multiple inheritance from both Student and Employee tables.

My Question: in SQL how to create the StudentAsst, and what is its primary key?

Databases don't have inheritance, multiple or otherwise. Each table is independent. — Barmar
– Barmar, Commented Jun 1, 2013 at 7:03
what are the relations? employee to student, student to asst, employee to asst ? (one to many etc) — Sharky
– Sharky, Commented Jun 1, 2013 at 7:06

dlebech · Accepted Answer · 2013-06-01 07:16:19Z

1

Since a StudentAsst is-a Student and is-an Employee, your table could have a primary key consisting of EmployeeID and StudentID:

CREATE TABLE StudentAsst
(StudentID int, 
 EmployeeID int,
 ...,
 CONSTRAINT PK_StudentAsst PRIMARY KEY (StudentID, EmployeeID),
 CONSTRAINT FK_Employee FOREIGN KEY (EmployeeId)
            REFERENCES Employee(EmployeeID),
 CONSTRAINT FK_Student FOREIGN KEY (StudentID)
            REFERENCES Student(StudentID)
 )

Here is an example of a similar situation with Student, Teachers and Parents.

answered Jun 1, 2013 at 7:16

dlebech

1,83914 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dlebech Over a year ago

I should add as a note that even though this might answer your question, you should ask yourself why you need the StudentAsst table. If there are no fields unique to a student assistant, it would be easier to have foreign key from your Student table to the Employee table, indicating that a student is also an employee.

Collectives™ on Stack Overflow

How to implement multiple inheritance in SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related