7 ms·

But if I have a circle relation and a triangle relation, how do I create a foreign key for the favourite_shape column in my user relation?

laszlokorte 5y ago
Thinking a bit more about it I think this would be a very elegant solution for Tagged-Unions in SQL: - simply allow tagged unions for foreign key constraints: CONSTRAINT my_fk FOREIGN KEY (own_column) REFERENCES EITHER table_a (a_id) OR table_b (b_id) OR table_c (c_id) - add join syntax to join via defined foreign key: FROM own_table JOIN VIA my_fk The union FK would store an additional flag determining the target table and the JOIN VIA would switch on that flag and it would be allowed to use any columns from all target tables in the query but only the matching ones would be not null.
- goto11 5y ago
  If you put the foreign keys on the table_a/b/c tables, then you can express the same without having to introduce tagged unions.
  - laszlokorte 5y ago
    As I said in response to a sibling: But can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.
goto11 5y ago
You would have the foreign key in the circle/triangle relations pointing to favourite_shape rather than the other way around.
- laszlokorte 5y ago
  But can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.
  - goto11 5y ago
    You can do that with a check constraint, although I don't know if you consider it simple. It would be cool with better support for common constraint patterns.
    - laszlokorte 5y ago
      Could you give a concrete example for such a check constraint? I imagine each shape-table would need to have it's own check to test the existence of the same id in all other shape tables, which scales qudratically. Or if you inverse the FK to make the user-table reference the shape tables you would need multiple potential nullable columns and a check constrain that exactly one is non-null. And it would still not solve the query side of things.
      - Twisell 5y ago
        Are you by any chance generating GPT-3 comments as part of an experiment? If not please take time to actually dive into modern SQL concepts there is likely a way to achieve your goal but I personally consider that it's pretty rude to ask random strangers a proof for a complex and loosely defined problem in the comments section. Stack Exchange should be a better place for instance.
      - goto11 5y ago
        Check constraints are just SQL expressions, so in pesudocode it would be something like: (SELECT COUNT(*) shape JOIN square JOIN circle) = 1 How conveniently this can be expressed depend on the database system in question. But if we are talking about adding features to SQL, it could have built-in support for such a constraint, just as there is built-in support for say foreign key constraints.

laszlokorte 5y ago

Thinking a bit more about it I think this would be a very elegant solution for Tagged-Unions in SQL: - simply allow tagged unions for foreign key constraints: CONSTRAINT my_fk FOREIGN KEY (own_column) REFERENCES EITHER table_a (a_id) OR table_b (b_id) OR table_c (c_id) - add join syntax to join via defined foreign key: FROM own_table JOIN VIA my_fk The union FK would store an additional flag determining the target table and the JOIN VIA would switch on that flag and it would be allowed to use any columns from all target tables in the query but only the matching ones would be not null.

goto11 5y ago
If you put the foreign keys on the table_a/b/c tables, then you can express the same without having to introduce tagged unions.
- laszlokorte 5y ago
  As I said in response to a sibling: But can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.

goto11 5y ago

You would have the foreign key in the circle/triangle relations pointing to favourite_shape rather than the other way around.

laszlokorte 5y ago
But can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.
- goto11 5y ago
  You can do that with a check constraint, although I don't know if you consider it simple. It would be cool with better support for common constraint patterns.
  - laszlokorte 5y ago
    Could you give a concrete example for such a check constraint? I imagine each shape-table would need to have it's own check to test the existence of the same id in all other shape tables, which scales qudratically. Or if you inverse the FK to make the user-table reference the shape tables you would need multiple potential nullable columns and a check constrain that exactly one is non-null. And it would still not solve the query side of things.
    - Twisell 5y ago
      Are you by any chance generating GPT-3 comments as part of an experiment? If not please take time to actually dive into modern SQL concepts there is likely a way to achieve your goal but I personally consider that it's pretty rude to ask random strangers a proof for a complex and loosely defined problem in the comments section. Stack Exchange should be a better place for instance.
    - goto11 5y ago
      Check constraints are just SQL expressions, so in pesudocode it would be something like: (SELECT COUNT(*) shape JOIN square JOIN circle) = 1 How conveniently this can be expressed depend on the database system in question. But if we are talking about adding features to SQL, it could have built-in support for such a constraint, just as there is built-in support for say foreign key constraints.