#1
  1. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18

    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?
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18

    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
            )
    )
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    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
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    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.


    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.

IMN logo majestic logo threadwatch logo seochat tools logo