Excerpts from Florian Pflug's message of mar ago 09 15:41:00 -0400 2011:
> First, I'd like to see us support FKs which reference non-unqiue columns.
> As long as we restrict such FK constraints to ON UPDATE/DELETE RESTRICT
> (or NO ACTION), there's no conceptual difficulty in doing that. Yeah, I
> know that there'll probably be a lot of push-back on the grounds of standards
> compliance, but I still believe it'd be a nice feature.
>
> Second, there are lots of reasons for adding UNIQUE constraints to columns
> beside being on the referencing side of a FK constraint. If we make the
> lock strength taken by UPDATE depend on whether or not the UPDATE modified a
> column included in a UNIQUE constraint, we force people to drop UNIQUE
> constraints to avoid deadlocks, and thus indirectly support sloppy schema
> design.
>
> Couldn't we simply give the user a way to specify, per column, whether or
> not that column is a "KEY" column? Creating a foreign key constraint could
> still implicitly mark all referenced columns as KEY columns, but columns
> would no longer be "KEY" columns simply because they're part of a UNIQUE
> constraint. Users would be free to add arbitrary columns to the set of
> "KEY" columns by doing ALTER TABLE ALTER COLUMN SET KEY.
What you propose seems reasonable to me (allegedly not an expert in
keys). However, I don't see that it conflicts with my proposal in any
way -- I mean, on top of my patch you could build something that changes
what columns are considered "keys". This is why the "KEY SHARE" rowmark
option is going to be documented as "only for internal use", because it
might change depending on how we define foreign-key-referenceable
fields.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support