Обсуждение: Cannot reference system table

create table    "schemes" (
    "id"        integer        not null unique default nextval('schemes_scheme_id_seq'),
    "user_id"    integer        not null references "pg_user" ("usesysid"),
    "scheme_name"    varchar(32)    not null unique,
    "expired"    boolean        not null default false,
    primary key    ("id")
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'schemes_pkey'
for table 'schemes'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  Referenced relation "pg_user" is not a table

As you can hopefully see, I need to reference the postgres user table for data
integrity.  Can this be done?

Also, what can I do before creating the table to eliminate those notices?

Thanks,

--
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.

Casey Allen Shobe <cshobe@secureworks.net> writes:
> create table    "schemes" (
> ...
>     "user_id"    integer        not null references "pg_user" ("usesysid"),
> ...
> ERROR:  Referenced relation "pg_user" is not a table

> As you can hopefully see, I need to reference the postgres user table
> for data integrity.  Can this be done?

Nope :-(.  The immediate cause of that complaint is that pg_user isn't
a table; it's only a view on pg_shadow.  But even if you'd referenced
pg_shadow, the command would have been rejected.  The difficulty is that
foreign-key constraints require triggers, and we don't support
user-defined triggers on system catalogs.  (That would imply the ability
to run arbitrary user-defined code during system catalog updates, which
has a ton of problems that I won't enumerate here.)

            regards, tom lane