From james@lrgmail.com Thu Jul 12 20:21:22 2001 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-- From sszabo@megazone23.bigpanda.com Thu Jul 12 22:41:14 2001 Received: from megazone23.bigpanda.com ([216.136.151.41]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6CMfEa01223 for ; Thu, 12 Jul 2001 18:41:14 -0400 (EDT) (envelope-from sszabo@megazone23.bigpanda.com) Received: from localhost (sszabo@localhost) by megazone23.bigpanda.com (8.11.2/8.11.2) with ESMTP id f6CMf7e06583; Thu, 12 Jul 2001 15:41:07 -0700 (PDT) Date: Thu, 12 Jul 2001 15:41:07 -0700 (PDT) From: Stephan Szabo To: James Orr cc: pgsql-sql@postgresql.org Subject: Re: Referencing a view? In-Reply-To: <001f01c10b10$ce8528a0$1600000a@lrg.office> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200107/164 X-Sequence-Number: 3956 On Thu, 12 Jul 2001, James Orr wrote: > 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: Not right now, and actually you still wouldn't get something that would work even with a unique index. We'd have to be able to push the constraint conditions down into the two tables that you're unioning to make it work correctly (think about the triggers on the referenced table for update and delete - which in your case with a unique index would be safe to put the triggers on all the time in both, but some view conditions would be such that that wouldn't be sufficient). You *might* be able to add the triggers manually and have it work. I haven't tried, and wouldn't guarantee it at all... From grigoriy.vovk@linustech.com.cy Fri Jul 13 07:35:12 2001 Received: from parmastocks2.cylink.com.cy ([194.42.142.6]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6D7ZBa87819 for ; Fri, 13 Jul 2001 03:35:12 -0400 (EDT) (envelope-from grigoriy.vovk@linustech.com.cy) Received: from callisto.internal.linustech.com.cy (parmastocks.cylink.com.cy [194.42.135.92]) by parmastocks2.cylink.com.cy (Postfix) with ESMTP id 2250E34077; Fri, 13 Jul 2001 10:33:39 +0300 (EEST) Date: Fri, 13 Jul 2001 10:33:44 +0300 (EEST) From: "Grigoriy G. Vovk" X-X-Sender: To: James Orr Cc: Subject: Re: Referencing a view? In-Reply-To: <001f01c10b10$ce8528a0$1600000a@lrg.office> Message-ID: <20010713102221.T415-100000@callisto.internal.linustech.com.cy> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200107/166 X-Sequence-Number: 3958 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 From james@lrgmail.com Fri Jul 13 13:36:49 2001 Received: from megamail.dsl.net (megamail.dsl.net [209.87.64.97]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6DDama91253 for ; Fri, 13 Jul 2001 09:36:49 -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 JAA04072 for ; Fri, 13 Jul 2001 09:36:43 -0400 Message-ID: <002301c10ba1$90872260$1600000a@lrg.office> From: "James Orr" To: References: <20010713102221.T415-100000@callisto.internal.linustech.com.cy> Subject: Re: Referencing a view? Date: Fri, 13 Jul 2001 09:41:16 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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/168 X-Sequence-Number: 3960 Thanks for all the responses! The one from Grigoriy was particularly interesting, I hadn't thought of that approach. However, I came to the realization that if somebody changes their address, I don't want it to be changed on previous orders. So I think i'll change the orders table to contain the actual address information and use an INSERT ... SELECT instead. That way I can be sure I have an accurate record of all orders. Thanks! - James From grigoriy.vovk@linustech.com.cy Fri Jul 13 15:12:30 2001 Received: from parmastocks2.cylink.com.cy (parmastocks2.cylink.com.cy [194.42.142.6]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6DFCTa21047 for ; Fri, 13 Jul 2001 11:12:30 -0400 (EDT) (envelope-from grigoriy.vovk@linustech.com.cy) Received: from callisto.internal.linustech.com.cy (parmastocks.cylink.com.cy [194.42.135.92]) by parmastocks2.cylink.com.cy (Postfix) with ESMTP id 80E08340B1; Fri, 13 Jul 2001 18:12:05 +0300 (EEST) Date: Fri, 13 Jul 2001 18:12:13 +0300 (EEST) From: "Grigoriy G. Vovk" X-X-Sender: To: James Orr Cc: Subject: Re: Referencing a view? In-Reply-To: <002301c10ba1$90872260$1600000a@lrg.office> Message-ID: <20010713175036.P886-100000@callisto.internal.linustech.com.cy> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200107/169 X-Sequence-Number: 3961 Jul 13, 09:41 -0400, James Orr wrote: > Thanks for all the responses! The one from Grigoriy was particularly > interesting, I hadn't thought of that approach. > > However, I came to the realization that if somebody changes their address, I > don't want it to be changed on previous orders. So I think i'll change the > orders table to contain the actual address information and use an INSERT ... > SELECT instead. That way I can be sure I have an accurate record of all > orders. Yes, its really a problem... When information sometime changes but we want to keep old information for depending data. (If I understand correctly what about you are speaking). But, you can resolve the problem if will copy information about address for executed order in a different place as a text, not as a link to the table 'address'. So, after execution of an order you will take current address by the link and copy all information (may be, as one field everything - concat all fields from the address together). Its a one solution. Next - you can use many addresses for an entity, and work only with some of them, 'valid', or 'current', as do you like, not with all. So, you will have full addresses history - when was changed, etc. And the order will always links to correct address, even now current address is different. You can do it by add field 'current' bool in the table 'address' or create additional table 'current_address' which will have 'id_entity' and 'id_address', so if you will do a search on 'current_address' you got a list of current addresses, in case you will do a search on 'address' - you got a list of all addresses. I prefere to create additional table. May be I've explained not well - ask, I'll type an database structure example, for developers it may be easy way :) my best regards, ---------------- Grigoriy G. Vovk From josh@agliodbs.com Fri Jul 13 15:14:27 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6DFERa21540 for ; Fri, 13 Jul 2001 11:14:27 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 85336; Fri, 13 Jul 2001 08:14:22 -0700 From: "Josh Berkus" Subject: Re: Referencing a view? To: "James Orr" , X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Fri, 13 Jul 2001 08:14:22 -0700 Message-ID: In-Reply-To: <002301c10ba1$90872260$1600000a@lrg.office> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===85336====davinci.ethosmedia.com===_" X-Archive-Number: 200107/171 X-Sequence-Number: 3963 This is a multi-part MIME message --_===85336====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit James, > However, I came to the realization that if somebody changes their > address, I > don't want it to be changed on previous orders. So I think i'll > change the > orders table to contain the actual address information and use an > INSERT ... > SELECT instead. That way I can be sure I have an accurate record of > all > orders. Hey, if you really want to be sophisticated about it, you should have multiple addresses per customer, with perhaps a status selector to indicate which addresses are new and which are old. For my current Postgres app, it goes futher than that to support companies with many contacts and locations: Company Address Contact . CompanyID -----------CompanyID------------CompanyID . Name AddressID------------AddressID . Status/Type Status/Type . Address Name, Title This allows me to have mutliple addresses per company (including old addresses for historical compatibility) and multiple contacts at each address. There's also an elegant (at least I think so) way to maintain a "default" contact and address for each company. Of course, this all requires some sophistication in the user interface, as one has to make the user distinguish between address corrections (UPDATE) and new addresses (INSERT) which possibly replace old addresses (UPDATE SET STATUS = old). -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===85336====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===85336====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===85336====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===85336====davinci.ethosmedia.com===_--