I have a table that is connected with 2 other tables like so:

Main table

ID_1                    | ID_2
//Foreign key of table1  //Foreign key of table2

Table1

ID                   | Some information |More information
//connected with ID_1

Table2

ID                   | Some information | More information
//connected with ID_2

So in the main table, ID_1 or ID_2 can be set, but not both. The way it is now (2 foreign keys) aren't working because they're optional.

What would be my options here?

I know I can split it up to 2 tables but that's not how I want it.

Should I make them able to be NULL?

You could invert the direction of the key: have Table1 and Table2 each contain a column that references back to the Main Table and make these non-nullable. This forces the referential integrity that you want, at the cost of making queries a bit more difficult because you cannot start from the Main Table to query. Not impossible, but something to think about.

This is actually quite a common problem in ORM-frameworks with regards to mapping inheritance. The solutions often amount to not enforcing the constraints on the database and instead leaving it to the application code to enforce these rules as a tradeoff. Look into 'table per type' versus 'table per hierarchy' versus 'table per class'.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.