Thread: references to variable in another schema
Hi folks: I wanted to put a reference on a table in the public schema that references a column in a table in another schema. I tried: create table cellphone(emplid integer not null references admin.tbl_employee.employee_id); ERROR: cross-database references are not implemented: "admin.tbl_employee.employee_id" But these are within one database. Is there a way to do this or have I gotten my syntax messed up? Mija
On Nov 9, 2007 12:48 PM, Mija Lee <mija@scharp.org> wrote: > Hi folks: > > I wanted to put a reference on a table in the public schema that > references a column in a table in another schema. I tried: > > create table cellphone(emplid integer not null references > admin.tbl_employee.employee_id); > > ERROR: cross-database references are not implemented: > "admin.tbl_employee.employee_id" > > But these are within one database. Is there a way to do this or have I > gotten my syntax messed up? The hierarchy in postgresql is Server->database->schema->table->column How as "admin" created? By using "create schema admin" or something else? Sean
Mija Lee <mija@scharp.org> writes: > I wanted to put a reference on a table in the public schema that > references a column in a table in another schema. I tried: > create table cellphone(emplid integer not null references > admin.tbl_employee.employee_id); Should be create table cellphone(emplid integer not null references admin.tbl_employee(employee_id)); Or, if employee_id is the declared primary key of tbl_employee, you can just say create table cellphone(emplid integer not null references admin.tbl_employee); regards, tom lane
On Nov 9, 2007 12:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Mija Lee <mija@scharp.org> writes: > > I wanted to put a reference on a table in the public schema that > > references a column in a table in another schema. I tried: > > > create table cellphone(emplid integer not null references > > admin.tbl_employee.employee_id); > > Should be > > create table cellphone(emplid integer not null > references admin.tbl_employee(employee_id)); > > Or, if employee_id is the declared primary key of tbl_employee, you can > just say > > create table cellphone(emplid integer not null > references admin.tbl_employee); Thanks, Tom. I was woefully off-target. Sean
Works like a charm. Thanks Tom & Sean for the quick response! Tom Lane wrote: > Mija Lee <mija@scharp.org> writes: >> I wanted to put a reference on a table in the public schema that >> references a column in a table in another schema. I tried: > >> create table cellphone(emplid integer not null references >> admin.tbl_employee.employee_id); > > Should be > > create table cellphone(emplid integer not null > references admin.tbl_employee(employee_id)); > > Or, if employee_id is the declared primary key of tbl_employee, you can > just say > > create table cellphone(emplid integer not null > references admin.tbl_employee); > > regards, tom lane