Cannot reference system table

Поиск
Список
Период
Сортировка
Искать

Cannot reference system table

От:
Casey Allen Shobe <cshobe@secureworks.net>
Дата:
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.

Re: Cannot reference system table

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Casey Allen Shobe  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
FAQ