Re: Foreign Key issue - pg_shadow

> "Rod Taylor" <rod.taylor@inquent.com> writes:
> > I suppose the below reference isn't intended to be done.  But, I'd
> > like to extend the pg_shadow table to store information about the
> > database users and some of their actions (a history of changes).
> > Is it safe to add a UNIQUE constraint to the usesysid or usename
> > columns?
> 
> You can't do either of those things, at least not without modifying
> code in the backend.  The code that manipulates pg_shadow entries knows
> exactly what indexes exist on the table, so you can't just go and create
> more.  Also, that code will not fire triggers, so even if the correct
> unique index existed, you would not get correct referential integrity
> behavior.
> 
> However, it does seem like an oversight that we don't use unique indexes
> to enforce uniqueness of usename and usesysid.  Too late to fix it for
> 7.1 (unless we want to force another initdb), but it ought to get fixed.
> Bruce, a TODO item please?

Added to TODO:
* Add unique indexes to pg_shadow.usename and pg_shadow.usesysid

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026