Re: Foreign Key issue - pg_shadow

> "Rod Taylor" <rod(dot)taylor(at)inquent(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026