Re: Allow foreign keys to reference a superset of unique columns
От | Wolfgang Walther |
---|---|
Тема | Re: Allow foreign keys to reference a superset of unique columns |
Дата | |
Msg-id | 79580b8d-1b38-72ce-0040-b62e2e46c35f@technowledgy.de обсуждение исходный текст |
Ответ на | Re: Allow foreign keys to reference a superset of unique columns (James Coleman <jtc331@gmail.com>) |
Ответы |
Re: Allow foreign keys to reference a superset of unique columns
(James Coleman <jtc331@gmail.com>)
|
Список | pgsql-hackers |
James Coleman: > So the broader point I'm trying to make is that, as I understand it, > indexes backing foreign key constraints is an implementation detail. > The SQL standard details the behavior of foreign key constraints > regardless of implementation details like a backing index. That means > that the behavior of two column foreign key constraints is defined in > a single way whether or not there's a backing index at all or whether > such a backing index, if present, contains one or two columns. > > I understand that for the use case you're describing this isn't the > absolute most efficient way to implement the desired data semantics. > But it would be incredibly confusing (and, I think, a violation of the > SQL standard) to have one foreign key constraint work in a different > way from another such constraint when both are indistinguishable at > the constraint level (the backing index isn't an attribute of the > constraint; it's merely an implementation detail). Ah, thanks, I understand better now. The two would only be indistinguishable at the constraint level, if $subject was implemented by allowing to create unique constraints on a superset of unique columns, backed by a different index (the suggestion we both made independently). But if it was possible to reference a superset of unique columns, where there was only a unique constraint put on a subset of the referenced columns (the idea originally introduced in this thread), then there would be a difference, right? That's if it was only the backing index that is not part of the SQL standard, and not also the fact that a foreign key should reference a primary key or unique constraint? Anyway, I can see very well how that would be quite confusing overall. It would probably be wiser to allow something roughly like this (if at all, of course): CREATE TABLE bar ( b INT PRIMARY KEY, f INT, ftype foo_type GENERATED ALWAYS AS REFERENCE TO foo.type, FOREIGN KEY (f, ftype) REFERENCES foo (f, type) ); It likely wouldn't work exactly like that, but given a foreign key to foo, the GENERATED clause could be used to fetch the value through the same triggers that form that FK for efficiency. My main point for now is: With a much more explicit syntax anything near that, this would certainly be an entirely different feature than $subject **and** it would be possible to implement on top of $subject. If at all. So no need for me to distract this thread from $subject anymore. I think the idea of allowing to create unique constraints on a superset of the columns of an already existing unique index is a good one, so let's discuss this further. Best Wolfgang
В списке pgsql-hackers по дате отправления: