CODEWITHSUNDEEP
sqldatabase
nutella
nutella

Reputation: 99

Can I have one column for two different values with foreign key from two different tables?

So I have this table Addresses:

CREATE TABLE Addresses
(
Address_ID VARCHAR (10) PRIMARY KEY,
Student_ID/Staff_Number VARCHAR (20),
Name CHAR (50),
Surname CHAR (30),
Building/House_Number INT (5),
Street CHAR (20),
City CHAR (30),
Postcode VARCHAR (10)
FOREIGN KEY (Student_ID) REFERENCES Students (Student_ID)
);

The thing is the line Student_ID/Staff_Number VARCHAR (20), is either coming from students table or staff table. So can I assign two foreign keys?

Upvotes: 4

Views: 1227

Answers (3)

Kevin
Kevin

Reputation: 2243

Okay, this is less of a "how do I do specific thing X?" and more of a "How should I be doing this?"

Because, to be honest, you don't want to do it the way you're trying to lay out.

Let me give you some alternatives that are better from a database layout perspective:

  1. Reverse the relationship (my recommendation). Instead of the Address table tying to the Staff/Student, have the Staff/Student tying to an AddressID.
  2. Consolidate the two tables into a person table (another very good choice). That way, you only have one table that the Address table is linking to. Then, if certain fields are applicable to only one type of person (staff-only, student-only, etc) - offload only those columns into a dedicated table for only those fields. So you might have a Person table with all the basic info, an Address table that links to the Person table, a Student table with a GradeLevel column, and a Staff table with a SubjectTaught column.
  3. Have two separate address tables: one for students, one for staff. Want to link over to the students table? Great - you've got the StudentID in the StudentAddress table. And the same with the Staff.
  4. Have a column indicating the AddresseeType. 0 for Students, 1 for Staff. Don't put the foreign key constraint in at all.
  5. Have two separate columns - one for the student ID, one for the staff ID (presumably, one will always be null.) Make them both a foreign key to the respective table (foreign keys can have null values.)

... all of these sound better than trying to make a split-foreign-key. Worse, what happens if a student and a staff member share an ID? Which entity does that address apply to?

Upvotes: 4

The Impaler
The Impaler

Reputation: 48865

You can always add multiple foreign key constraints on a single column but ALL of them will be enforced, not just one of them. For example:

CREATE TABLE Addresses
(
Address_ID VARCHAR (10) PRIMARY KEY,
Student_ID VARCHAR (20),
Name CHAR (50),
Surname CHAR (30),
Building/House_Number INT (5),
Street CHAR (20),
City CHAR (30),
Postcode VARCHAR (10),
FOREIGN KEY (Student_ID) REFERENCES Students (Student_ID)
FOREIGN KEY (Student_ID) REFERENCES Staff (Staff_ID)
);

This is probably not what you want. Probably what you want is to have two columns, each one pointing to a separate table; and for those to columns one is always null and the other one is not null. You can do this as shown below:

CREATE TABLE Addresses
(
Address_ID VARCHAR (10) PRIMARY KEY,
Student_ID VARCHAR (20),
Staff_ID VARCHAR(20),
Name CHAR (50),
Surname CHAR (30),
Building/House_Number INT (5),
Street CHAR (20),
City CHAR (30),
Postcode VARCHAR (10),
FOREIGN KEY (Student_ID) REFERENCES Students (Student_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID),
constraint ct1 check (Student_ID is null and Staff_ID is not null
                   or Student_ID is not null and Staff_ID is null)
);

The constraint ct1 makes sure one and only one of them is "active" at any given point in time.

Upvotes: -1

Milney
Milney

Reputation: 6427

No, that is not possible.

You would probably be looking at implementing some kind of inheritance pattern in your design where you have something like a 'person' base table which both student and staff tables extend and the foreign key points to that instead.

P.S. Is your varchar Ids columns the clustered indexes? This can be bad for performance, consider a surrogate identity column for clustering

Upvotes: 0

Related Questions