From: Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
To: James Orr <james@lrgmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Referencing a view?
Date: Fri, 13 Jul 2001 10:33:44 +0300 (EEST)
Message-ID: <20010713102221.T415-100000@callisto.internal.linustech.com.cy> (raw)
In-Reply-To: <001f01c10b10$ce8528a0$1600000a@lrg.office>
Jul 12, 16:25 -0400, James Orr wrote:
Much better will be change database structure - you have absolutely
identical tables, so is not good from normalization point of view.
Better use one table for address, and link it to one table for
"entity" - both person and company, and "entity" link to specific
information about person and company.
Somathing like this:
create table entity(
id_entity serial not null primary key,
_all_other_fields_,
.........);
create table address(
id_address serial not null primary key,
id_entity integer not null references entity,
_all_other_fields_,
.........);
create table person(
id_entity integer not null primary key references entity,
first_name text,
last_name text,
...........);
create table company(
id_entity integer not null primary key references entity,
company_name text,
........);
It may be usefull to add column 'who_is' boolean in the 'entity' table -
when you will do a search on 'address' table you will get 'id_entity',
and you can do a search on 'entity' table and get 'who_is', and than you
can get other information fom 'person' or 'company' tables.
> Hi,
>
> Is there anyway that you can reference a column in a view for referential integrity? The problem is with the unique thing, obviously I can't create a unique index on a view. Here is what I have:
>
> CREATE SEQUENCE "addresses_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
>
> CREATE TABLE "org_addresses" (
> "id" integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL,
> "orgid" integer references orgs on delete cascade,
> "name" character varying(255),
> "street1" character varying(255),
> "street2" character varying(100),
> "city" character varying(100),
> "state" character(2),
> "zip" character(10),
> Constraint "org_addresses_pkey" Primary Key ("id")
> );
>
> CREATE TABLE "user_addresses" (
> "id" integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL,
> "userid" integer references users on delete cascade,
> "name" character varying(255),
> "street1" character varying(255),
> "street2" character varying(100),
> "city" character varying(100),
> "state" character(2),
> "zip" character(10),
> Constraint "user_addresses_pkey" Primary Key ("id")
> );
>
> CREATE VIEW "addresses" as SELECT user_addresses.id, user_addresses.userid, user_addresses.name, user_addresses.street1, user_addresses.street2, user_addresses.city, user_addresses.state, user_addresses.zip FROM user_addresses UNION SELECT org_addresses.id, NULL::unknown, org_addresses.name, org_addresses.street1, org_addresses.street2, org_addresses.city, org_addresses.state, org_addresses.zip FROM org_addresses;
>
> So this gives me a view with every address, each with a unique id as I used the same sequence in both tables. Now what I want to do is something like this :
>
> CREATE TABLE orders (
> id serial primary key,
> shipping_address int references addresses(id),
> .
> .
> );
>
> Which of course doesn't work because addresses as a view can't have a unique index. Any way around this?
>
> - James
>
my best regards,
----------------
Grigoriy G. Vovk
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grigoriy.vovk@linustech.com.cy, james@lrgmail.com
Subject: Re: Referencing a view?
In-Reply-To: <20010713102221.T415-100000@callisto.internal.linustech.com.cy>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox