You can retain foreign key constraints by having one column per type of reference. It is also possible to ensure that exactly one column of several is NOT NULL, so that the columns can always be mapped to an enum in application code. Also, in PostgreSQL, the storage for the extra NULLs uses just one bit per column in a bitmap.
CREATE TABLE dirents (
parent bigint NOT NULL,
child_dir bigint,
child_file bigint,
child_symlink bigint,
basename text NOT NULL,
-- Ensure exactly one type of child is set
CHECK (num_nonnulls(child_dir, child_file, child_symlink) = 1),
CONSTRAINT dirents_child_dir_fkey FOREIGN KEY (child_dir) REFERENCES dirs (id),
CONSTRAINT dirents_child_file_fkey FOREIGN KEY (child_file) REFERENCES files (id),
CONSTRAINT dirents_child_symlink_fkey FOREIGN KEY (child_symlink) REFERENCES symlinks (id),
PRIMARY KEY (parent, basename)
);
Re:Show HN: PRQL 0.2 – a better SQL
by slaymaker1907 on Monday June 27, 2022 @08:07PM
(#31902540)
Yep, and you can also solve this problem by having a separate table for each variant and joining whenever you need to deal with common stuff. Personally, I think it's usually better just to have one giant table and allow the different columns to be null since it lets you avoid a bunch of verbose joins.
(No foreign key constraints, but those are falling out of use in some cases due to inability to online migrate mysql schemas anyways.)