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@xxxxxxxxxxxxxxxxx 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 // ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
