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

Question

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?

Answer 1

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?