Re: Moving to postgresql and some ignorant questions

Phoenix Kiula wrote:
> Agree. That's what I am trying to do. Include as many UPDATEs etc into
> the same TRANSACTION block, but my worry is when I read about
> autocommit and how it is enabled by default in postgresql 8.
> Specifying explicit BEGIN and COMMIT blocks should only commit when I
> want the DB to, or will each UPDATE in the middle of this block get
> executed?

The database doesn't have autocommit, AFAIK.
Some of the clients have, but it only applies if you don't put your SQL
statements between BEGIN; and COMMIT; (or ROLLBACK;) statements.

I never really use anything but psql for a client, so I can'treally say
how other clients (pgadmin fe.) handle this.


>> Incidentally, most data integrity checks are handled with CHECK constraints
>> and FOREIGN KEY constraints rather than manual triggers. They're both easier
>> and cheaper.
>
> The problem with simple CHECK constraints is that they can only
> reference the primary key in another table. What if I want more than
> one column to be the same as the referenced table, but do not want to
> have a compound primary key in the referenced table? From reading and
> re-reading the manual, I dont think FKs allow for this. Only primary
> key references are supported.

You're confusing CHECK constraints and FOREIGN KEY constraints. They're
different things ;)

CHECK constraints verify that data in a certain column matches a certain
condition. I'm not sure they can reference columns in other tables,
unless you wrap those checks in stored procedures maybe...

For example:
CREATE TABLE test (
    age int NOT NULL CHECK (age > 0)
);

Next to that, you can define DOMAINs - basically your own customized
data types that can follow _your_ rules. Admittedly I have never done
that yet, but it's supposed to be one of the key features of the
relational model (I've seen claims that you're actually not supposed to
use the base types, but define domains for all your data types).

*And* you can define compound foreign key constraints,
for example:

CREATE TABLE employee (
    employee_id serial NOT NULL,
    company_id integer NOT NULL,
    name text NOT NULL,
    CONSTRAINT employee_pk
        PRIMARY KEY (employee_id, company_id)
);

CREATE TABLE division (
    employee_id integer NOT NULL,
    company_id integer NOT NULL,
    name text NOT NULL,
    CONSTRAINT division_fk
        FOREIGN KEY (employee_id, company_id)
            REFERENCES employee
            ON DELETE SET NULL
            ON UPDATE CASCADE
);


Also a nice trick, when performing DDL statements (CREATE TABLE and
friends), you can wrap them in a transaction and commit (or rollback) if
you like the end result (or not). I believe the only exception to that
rule is CREATE DATABASE.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //