PostgreSQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesPostgreSQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old August 9th, 2002, 07:48 PM
rod k rod k is offline
Apprentice Deity
Dev Shed Loyal (3000 - 3499 posts)
 
Join Date: Jul 1999
Location: Niagara Falls (On the wrong side of the gorge)
Posts: 3,237 rod k User rank is Private First Class (20 - 50 Reputation Level)rod k User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 4 m 8 sec
Reputation Power: 17
Send a message via AIM to rod k
Trouble writing table constraint

Here's the need:

Table contains addresses that MUST support both US and Canadian addresses.

state and province field are declared as null and reference appropriate tables (state and province).
country is not null and checks for only values 1 and 2 (US and Canada)
zipcode is not null and checked vs regular expression
postalcode is not null and checked vs regular expression

Now, what I need is a table constraint that will verify that state and zipcode are not null if country==1 and that province and postalcode are not null if country==2.

This is the alter query I used:
Code:
ALTER TABLE addresses
ADD CHECK (
CASE
	WHEN country=1 THEN state IS NULL AND zipcode IS NULL
	WHEN country=2 THEN province IS NULL AND postalcode IS NULL
	ELSE true
END
)


But I get the following error:

ERROR: cache lookup of attribute 15 in relation 110121 failed

BTW, two tables inherit from this table, if that makes a difference.

Anybody experience this or have any suggestions?
__________________
FSBO (For Sale By Owner) Realty

Reply With Quote
  #2  
Old August 10th, 2002, 10:31 AM
rod k rod k is offline
Apprentice Deity
Dev Shed Loyal (3000 - 3499 posts)
 
Join Date: Jul 1999
Location: Niagara Falls (On the wrong side of the gorge)
Posts: 3,237 rod k User rank is Private First Class (20 - 50 Reputation Level)rod k User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 4 m 8 sec
Reputation Power: 17
Send a message via AIM to rod k
What I've learned

1) Postgres does NOT propogate constraints to children when you modify the parent

2) In fact, adding a table constraint to any table (with or without children) is often not possible with complicated constraints.

If ne1 is interested I ended up having to drop the table (and it's children) and recreate it. Below is the CREATE query:

Code:
create table addresses
(
    addressID SERIAL8 NOT NULL PRIMARY KEY,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(64) NOT NULL,
    state INT2 REFERENCES states(stateID) ON UPDATE CASCADE,
    canProv INT2 REFERENCES provinces(provID) ON UPDATE CASCADE,
    provName VARCHAR(64),
    zip CHAR(10),
    canZip CHAR(7),
    postalCode VARCHAR(12),
    country INT2 NOT NULL REFERENCES countries(countryID) ON UPDATE CASCADE,
    CONSTRAINT checkAddress
        CHECK
        (
            CASE
                WHEN country=1 
                    THEN state IS NOT NULL 
                    AND zip IS NOT NULL 
                    AND zip ~ '^[0-9]{5}(-[0-9]{4})?$' 
                    AND canProv IS NULL 
                    AND canZip IS NULL
                    AND provName IS NULL
                    AND postalCode IS NULL
                WHEN country=2 
                    THEN canProv IS NOT NULL 
                    AND canZip IS NOT NULL 
                    AND canZip ~ '^[A-Z][0-9][A-Z] [0-9][A-Z][0-9]$'
                    AND zip IS NULL
                    AND state IS NULL
                    AND provName IS NULL
                    AND postalCode IS NULL
                WHEN country>2 
                    THEN state IS NULL
                    AND zip IS NULL
                    AND canProv IS NULL
                    AND canZip IS NULL
                ELSE FALSE
            END
        )
)

Reply With Quote
  #3  
Old August 10th, 2002, 11:23 AM
rycamor rycamor is offline
Gödelian monster
Dev Shed Regular (2000 - 2499 posts)
 
Join Date: Jul 1999
Location: Central Florida, USA
Posts: 2,306 rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level)rycamor User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 3 Days 6 h 42 m 51 sec
Reputation Power: 60
Yes, unfortunately, PostgreSQL's inheritance mechanism is really one of the oldest parts of the code, and according to Bruce M. and Tome Lane, has not really been updated since PostgreSQL was turned into an open source project. Basically, that is pure Ingres code, from the 80's. And no, inheritance in PG never propagates as changes are made to the parent. It is a very limited inheritance model.

FYI, there has been some discussion in the [pgsql-hacker] lists about whether to start extending the inheritance behavior, or to eventually just drop inheritance altogether . (Some claim that a proper relational database should be able to handle any logical operation that one would use inheritance for.) I haven't thought through that one all the way, but I hope PG keeps inheritance, and extends it according to the ideas in The Third Manifesto by Chris Date.

Anyway, at the moment, I am a little leery of depending too much on inheritance until I see where they are going with it.

Also, PostgreSQL in general is not really "change-friendly". Often, when I am making big changes in structure, such as with relationships and constraints, I just dump the whole DB, re-arrange things, and re-create.

The error message you received above is familiar to anyone who has tried too many structural changes on a live PG database. Oftne, if you drop and re-create a table that other objects are depending on, it causes problems like this. (functions especially don't like tables to disappear and re-appear) I'm hoping version 7.3 will take care of some of these problems.
__________________
The real n-tier system:

FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

Amazon wishlist -- rycamor (at) gmail.com

Reply With Quote
  #4  
Old August 10th, 2002, 03:06 PM
rod k rod k is offline
Apprentice Deity
Dev Shed Loyal (3000 - 3499 posts)
 
Join Date: Jul 1999
Location: Niagara Falls (On the wrong side of the gorge)
Posts: 3,237 rod k User rank is Private First Class (20 - 50 Reputation Level)rod k User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 4 m 8 sec
Reputation Power: 17
Send a message via AIM to rod k
Quote:
there has been some discussion in the [pgsql-hacker] lists
I read some of those when I was searching for solutions, but the posts were a bit dated. Inheritence is a powerful feature and I hope it stays but it'll definately need some work before it's really useful. Not being able to have modified constraits cascade to the children is a major drawback.


Quote:
PostgreSQL in general is not really "change-friendly".
As I've discovered
I've stuck with MySQL longer than I should have. Coding around transactions and constraints was fine as long as I was the only one writing the code that would access the DB. I really wanted to change but the benefit vs cost (time spent) didn't make it possible. (Un)fortunately, I know have to worry about other coders accessing DBs I design, so DB constraints are a must.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesPostgreSQL Help > Trouble writing table constraint

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap