Received: from megamail.dsl.net (megamail.dsl.net [209.87.64.97]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6CKKwa45274 for ; Thu, 12 Jul 2001 16:21:22 -0400 (EDT) (envelope-from james@lrgmail.com) Received: from jameslt (64-51-205-180.client.dsl.net [64.51.205.180]) by megamail.dsl.net (8.9.3/8.9.3) with SMTP id QAA25914 for ; Thu, 12 Jul 2001 16:20:31 -0400 Message-ID: <001f01c10b10$ce8528a0$1600000a@lrg.office> From: "James Orr" To: Subject: Referencing a view? Date: Thu, 12 Jul 2001 16:25:04 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0016_01C10AEF.34D1A2E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200107/163 X-Sequence-Number: 3955 This is a multi-part message in MIME format. ------=_NextPart_000_0016_01C10AEF.34D1A2E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, Is there anyway that you can reference a column in a view for referential i= ntegrity? 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_addresse= s UNION SELECT org_addresses.id, NULL::unknown, org_addresses.name, org_add= resses.street1, org_addresses.street2, org_addresses.city, org_addresses.st= ate, 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), =2E =2E ); Which of course doesn't work because addresses as a view can't have a uniqu= e index. Any way around this? - James ------=_NextPart_000_0016_01C10AEF.34D1A2E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
Is there anyway that you can reference a c= olumn in=20 a view for referential integrity?  The problem is with the unique thin= g,=20 obviously I can't create a unique index on a view.  Here is what I=20 have:

CRE= ATE SEQUENCE=20 "addresses_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 = =20 cache 1 ;
 
CREATE TABLE "org_addresses" (
 "i= d"=20 integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL,
 "orgid= "=20 integer references orgs on delete cascade,
 "name" character=20 varying(255),
 "street1" character varying(255),
 "street2"= =20 character varying(100),
 "city" character varying(100),
 "s= tate"=20 character(2),
 "zip" character(10),
 Constraint=20 "org_addresses_pkey" Primary Key ("id")
);
 
CREATE TABLE "user_addresses" (
 "= id"=20 integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL,
 "useri= d"=20 integer references users on delete cascade,
 "name" character=20 varying(255),
 "street1" character varying(255),
 "street2"= =20 character varying(100),
 "city" character varying(100),
 "s= tate"=20 character(2),
 "zip" character(10),
 Constraint=20 "user_addresses_pkey" Primary Key ("id")
);
 
CREATE VIEW "addresses" as SELECT=20 user_addresses.id, user_addresses.userid, user_addresses.name,=20 user_addresses.street1, user_addresses.street2, user_addresses.city,=20 user_addresses.state, user_addresses.zip FROM user_addresses UNION SELECT= =20 org_addresses.id, NULL::unknown, org_addresses.name, org_addresses.street1,= =20 org_addresses.street2, org_addresses.city, org_addresses.state,=20 org_addresses.zip FROM org_addresses;
So this gives me a view with every address= , each=20 with a unique id as I used the same sequence in both tables.  Now what= I=20 want to do is something like this :
 
CREATE TABLE orders (
    id serial primary=20 key,
    shipping_address int re= ferences=20 addresses(id),
.
.
);
 
Which of course doesn't work because= =20 addresses as a view can't have a unique index.  Any way around=20 this?
 
- James
------=_NextPart_000_0016_01C10AEF.34D1A2E0--