pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (6+ messages)  latest in thread

Message-ID: <20010713102221.T415-100000@callisto.internal.linustech.com.cy>
Permalink:  ../20010713102221.T415-100000@callisto.internal.linustech.com.cy/
Also on:    postgresql.org/message-id/20010713102221.T415-100000@callisto.internal.linustech.com.cy

 · 

reply

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