I've come up with this example and I want to know why it does what it
does.
-- snip --
You are now connected to database template1.
CREATE DATABASE
You are now connected to database testing.
psql:test2.sql:11: NOTICE: CREATE TABLE/UNIQUE will create implicit
index 'subdivs_name_key' for table 'subdivs'
psql:test2.sql:11: NOTICE: CREATE TABLE/PRIMARY KEY will create
implicit index 'subdivs_pkey' for table 'subdivs'
CREATE
psql:test2.sql:20: NOTICE: CREATE TABLE will create implicit sequence
'blah_id_seq' for SERIAL column 'blah.id'
psql:test2.sql:20: NOTICE: CREATE TABLE/PRIMARY KEY will create
implicit index 'blah_pkey' for table 'blah'
psql:test2.sql:20: NOTICE: CREATE TABLE will create implicit
trigger(s) for FOREIGN KEY check(s)
CREATE
INSERT 218198 1
BEGIN
INSERT 218199 1
psql:test2.sql:29: ERROR: triggered data change violation on relation
"blah"
ROLLBACK
BEGIN
INSERT 218200 1
UPDATE 1
DELETE 1
ROLLBACK
-- snip --
and the test file is attached.
--
hackers ally
\c template1
-- drop database testing;
create database testing with encoding='SQL_ASCII';
\c testing
create table subdivs (
code char(10) not null,
name char(35) unique not null,
primary key (code)
);
create table blah (
id serial,
subdiv_code char(10),
primary key (id),
foreign key (subdiv_code)
references subdivs
);
insert into subdivs (code, name)
values ('VG', 'Village');
begin;
insert into blah (subdiv_code)
values ('VG');
delete from blah where subdiv_code='VG';
abort;
begin;
insert into blah (subdiv_code)
values ('VG');
update blah set subdiv_code='VG' where subdiv_code='VG';
delete from blah where subdiv_code='VG';
abort;
PGP signature
