Can i create a table with 2 foreign keys? These 2 foreign keys are 2 primary keys of 2 different tables

Question

I am planning to create a table with 2 foreign keys. These 2 foreign keys reference 2 primary keys of 2 different tables. However, one of the two foreign keys may be null due to business reasons. So can I create this table?

Answer 1

Without knowing which RDBMS this is for, the syntax might be slightly off - but sure, you can definitely have two foreign keys in a single table, referencing two other tables - and sure, one of them can be nullable. The referenced primary key columns in the two master tables you reference typically however cannot be NULL; most RDBMS do NOT allow NULL in a primary key.

In SQL Server, you would set it up something like this:

ALTER TABLE dbo.YourTable
    ADD CONSTRAINT FK_YourFirstForeignKey
        FOREIGN KEY (YourFirstColumn) REFRENCES dbo.MasterTable1(Column1);

ALTER TABLE dbo.YourTable
    ADD CONSTRAINT FK_YourSecondForeignKey
        FOREIGN KEY (YourSecondColumn) REFRENCES dbo.MasterTable2(Column2);

YourTable would have to have two columns (assuming INT datatype here - adapt as needed):

YourFirstColumn INT NOT NULL,
YourSecondColumn INT NULL

The YourFirstColumn is a must-have column, while YourSecondColumn can be null.

And of course - you could also define and include those FK constraints directly into your CREATE TABLE statement, if you prefer to do that.