Re: [HACKERS] RI generic trigger procs
От | wieck@debis.com (Jan Wieck) |
---|---|
Тема | Re: [HACKERS] RI generic trigger procs |
Дата | |
Msg-id | m11WRVM-0003kLC@orion.SAPserv.Hamburg.dsh.de обсуждение исходный текст |
Ответ на | Re: [HACKERS] RI generic trigger procs (Hannu Krosing <hannu@trust.ee>) |
Список | pgsql-hackers |
Hannu Krosing wrote: > > Jan Wieck wrote: > > > > Any combination of attributes in a table referenced to by one > > or more FOREIGN KEY ... REFERENCES constraint of another > > table shall have a UNIQUE and NOT NULL constraint. > ... > > So we assume here that any PK is unique and cannot contain NULL's. > > What is the reasoning behind requiring this ? > > I can't see anything that would mandate this - > * NULLs are'nt equal anyway and ar even disregarded under your > current description. > Or are you just protecting yourself against the case where the > foreign key field is set to null - could this be handled the > same as deleting for cascaded constraints ? MATCH FULL (as I planned to implement for now) mandates that either none or all fields of foreign key are NULL. Of course, we could handle the UPDATE of referenced key (PK) from none NULL to some/all NULL as if the operation was DELETE. And similar treating UPDATE/DELETE where OLD had NULL(s) as nothing, since according to MATCH FULL absolutely no reference can exist. When looking ahead it's better to add one more argument to the trigger proc's specifying the MATCH type. That way we could add support for MATCH PARTIAL by only working on the trigger procs with no need to touch anything else in the system. This will be the 4th argument before the attribute name pairs and containts either 'FULL' or 'PARTIAL'. Support for MATCH PARTIAL is alot more complicated though - thus I left it for later. Let's see how fast we could get this all to work and then decide if it's something to include in this or one of the next releases. > * UNIQUE would save us the check for existing other possible > referenced values - is this mandated by SQL spec ? SQL3 specification X3H2-93-359 and MUN-003 11.9 <referential constraint definition> 2) Case: a) If the <referenced table and columns> specifies a <reference column list>, then the set of column names of that <refer- ence column list> shall be equal to the set of column names in the unique columns of a unique constraint of the refer- enced table. Let referenced columns be the column or columns identified by that <reference column list> and let refer- enced column be one such column. Each referenced column shall identify a column of the referenced table and the same column shall not be identified more than once. b) If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that spec- ifies PRIMARY KEY. Let referenced columns be the column or columns identified by the unique columns in that unique con- straint and let referenced column be one such column. The <referenced table and columns> shall be considered to implic- itly specify a <reference column list> that is identical to that <unique column list>. So the UNIQUE constraint on the referenced columns of the referenced table is mandatory. And the spec also tells that the UNIQUE constrain on the referenced columns shall NOT be deferrable, so our (mis)usage of a unique index for uniqueness doesn't break the specs here. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
В списке pgsql-hackers по дате отправления: