{{ message }}
Ability to add constraint "hints" on views #11119
teaguesterling
started this conversation in
Ideas
Replies: 1 comment
-
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment

Uh oh!
There was an error while loading. Please reload this page.
-
Apologies in advance if this is out of left-field.
It would be helpful for discoverability if there was a way to define relationships or (unenforced) constraints on views or table macros. These would obviously not be verifiable as the underlying data will change externally. However it would help in communicating intent or expected relationships/values within a schema.
This would not really be possible until
ALTER .. ADD CONSTRAINTis implemented, so this would be a ways off. Maybe not refer to it as a "constraint" at all but instead an expectation or a validation hint. That could be a useful way to also add non-enforced constraints to tables as well to avoid the performance hit of creating an index.The specific case I am facing is trying to describe foreign key constraints between columns in external files exposed through views. This would not serve any sort of verification but would be a standard (and introspection friendly) way to document the relationships between entities and facilitate introspection.
I imagine this working something like this:
COPY ( FROM VALUES (1, 'a', 1.23), (2, 'b', 4.56), (3, 'c', 3) AS _(id, name, value) ) TO 'test.json' (FORMAT json); COPY ( FROM VALUES (1, 'Letter A'), (2, 'Letter B'), (3, 'Letter C'), (4, 'Missing') AS _(test_id, label) ) TO 'test.csv' (FORMAT csv); CREATE VIEW test_json AS FROM 'test.json'; CREATE VIEW csv_labels AS FROM 'test.csv'; ALTER VIEW json_values ADD CONSTRAINT PRIMARY KEY (id); ALTER VIEW csv_labels ADD CONSTRAINT FOREIGN KEY (test_id) REFERENCES json_values (id);There may be (much) better ways to do this, but having something akin to this would allow tools using DuckDB to automatically recognize predefined relations on external datasets accessed via DuckDB.
Beta Was this translation helpful? Give feedback.
All reactions