4 ms·

Ha, SQL is one of only a handful of computer languages that is both older than most of us and probably will outlive most of us.

• quectophoton 2y ago
  I just hope that PostgreSQL becomes able to do something as conveniently (keyword, "conveniently") as this within my lifetime: CREATE TABLE permission_overrides ( -- (...) granted_by_user_id BIGINT NOT NULL REFERENCES users (id), granted_to_user_id BIGINT NOT NULL REFERENCES users (id), -- Ensure both users belong to the same tenant. CHECK (DEREFERENCE(granted_by_user_id).tenant_id = DEREFERENCE(granted_to_user_id).tenant_id) ); (Or whatever similarly-convenient variation we want to bikeshed about.) I understand there are performance implications[1] with such a thing, but being able to prevent invalid data from even existing in the database is one of the main reasons I use a database instead of some dumb store, because I don't want to have weird data the moment the application has a major change (or worse, a rewrite). [1]: That specific example looks like it would be slow as hell unless that table is mostly append-only. (EDIT: And also that the referenced rows don't change too much.)
  • dragonwriter 2y ago
    While there are lots of cases where referencing "through" foreign key relations would be useful, this specific example is one for which dereferencing & check seems to be LESS convenient that what is available today with just composite foreign keys. CREATE TABLE permission_overrides ( -- (...) granted_in_tenant_id BIGINT NOT NULL, granted_by_user_id BIGINT NOT NULL, granted_to_user_id BIGINT NOT NULL, FOREIGN KEY (granted_by_user_id, granted_in_tenant_id) REFERENCES users (id, tenant_id), FOREIGN KEY (granted_to_user_id, granted_in_tenant_id) REFERENCES users (id, tenant_id) );