Multiple yet mutually exclusive foreign keys - is this the way to go?
If we look into the model here, we will see the following:
- A user is related to exactly one website
- A company is related to exactly one website
- A website is related to exactly one user or company
The third relation implies existence of a "user or company" entity whose PRIMARY KEY
should be stored somewhere.
To store it you need to create a table that would store a PRIMARY KEY
of a website owner
entity. This table can also store attributes common for a user and a website.
Since it's a one-to-one relation, website attributes can be stored in this table too.
The attributes not shared by users and companies should be stored in the separate table.
To force the correct relationships, you need to make the PRIMARY KEY
of the website
composite with owner type
as a part of it, and force the correct type in the child tables with a CHECK
constraint:
CREATE TABLE website_owner (
type INT NOT NULL,
id INT NOT NULL,
website_attributes,
common_attributes,
CHECK (type IN (1, 2)) -- 1 for user, 2 for company
PRIMARY KEY (type, id)
)
CREATE TABLE user (
type INT NOT NULL,
id INT NOT NULL PRIMARY KEY,
user_attributes,
CHECK (type = 1),
FOREIGN KEY (type, id) REFERENCES website_owner
)
CREATE TABLE company (
type INT NOT NULL,
id INT NOT NULL PRIMARY KEY,
company_attributes,
CHECK (type = 2),
FOREIGN KEY (type, id) REFERENCES website_owner
)
Two mutually exclusive many-to-many relationships
I decided to settle on my last idea. Normalize sizes
table, by creating intermediate productvariants
table, which holds entities representing things customers buy.
orders
table:
id int not null,
...
orders_productvariants
table:
order_id int not null,
productvariant_id int not null,
...
productvariants
table:
id int not null,
product_id int not null,
size_id int not null,
...
products
table:
id int not null,
...
sizes
table:
id int not null,
...
Additionally, I have a size with empty name, for items that have no size.
Can a linking table have two mutually exclusive columns?
Here's a thought to fix the problem without breaking much code. Create a newtable students and populate with the data from both the other tables and a flag field to indicate full or part time. Then create views that have the names of the full time students and part time students table. This means all selects will still work perfectly and all you will have to do is fix the insert and update code to go to the new table.
Or you could enforce your rules in a trigger.
Is there a way to restrict foreign keys references to another column putting custom rules on this references?
Yes, you can create a trigger before insert/update that will check whether exists an employee whose user_id
matches the value of employer_id
. If such a record exists, then throw an exception. See: https://www.postgresql.org/docs/9.1/plpgsql-trigger.html
Two foreign keys, one of them not NULL: How to solve this in SQL?
Your data model is fine. In most databases, you would also add a check
constraint:
alter table `time` add constraint chk_time_project_special_work
check (project is not null xor special_work is null);
However, MySQL does not support check constraints. You can implement the logic using a trigger, if you really like.
Join two tables through third one with two foreign keys
If you want to know the deposits in which a bank does not participate:
1- Add a related_name to deposit and bank:
class DepositProposal(models.Model):
percent = models.FloatField()
deposit = models.ForeignKey(Deposit, related_name = "proposals")
bank = models.ForeignKey(Bank, related_name = "proposals")
2- Get the bank that you want to check:
bank = Bank.objects.first() #For example, the first bank
3- Get the queryset:
deposits = Deposit.objects.exclude(proposals__bank = bank)
EDIT:
If you want to know the banks that not participate on a specific deposit:
deposit = Deposit.objects.first() #For example, the first deposit
banks = Bank.objects.exclude(proposals__deposit = deposit)
Two foreign keys, only one can be not null at a time
Use a check
constraint:
alter table t add constraint chk_table_fk1_fk2 on table
check (fk1 is null or fk2 is null);
Related Topics
Flattening Intersecting Timespans
How to Tell If I Have Uncommitted Work in an Oracle Transaction
JSONb Query with Nested Objects in an Array
How to Find the Record in a Table That Contains the Maximum Value
Clean Way to Use Postgresql Window Functions in Django Orm
How to Order by Parent Then Child
Postgresql Query to Detect Overlapping Time Ranges
Sum Columns with Null Values in Oracle
Omitting the Milliseconds in a Date
Sql: How to Use Union and Order by a Specific Select
Doing a Where in on Multiple Columns in Postgresql
SQL Update Query Syntax with Inner Join
Adding a Uniqueidentifier Column and Adding the Default to Generate New Guid
Counting Rows for All Tables at Once
Recursive Query Used for Transitive Closure
Update Values in Identity Column
How to Write a Function in the H2 Database Without Using Java