Bug #583: Wrong example in 7.2 Tutorial

Rainer Tammer (rainer.tammer@spg.schulergroup.com) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
Wrong example in 7.2 Tutorial

Long Description
Wrong referentical integrity example in PostgreSQL 7.2 Tutorial

Sample Code
Chapter 3.3. Foreign Keys
=========================

test=# CREATE TABLE cities (
test(# name varchar(80) primary key,
test(# location point
test(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'cities_pkey' for table 'cities'
CREATE
test=# CREATE TABLE weather (
test(# city varchar(80) references weather,
test(# temp_lo int,
test(# temp_hi int,
test(# prcp real,
test(# date date
test(# );
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  PRIMARY KEY for referenced table "weather" not found

I think the following is correct:

CREATE TABLE cities (
city varchar(80) primary key,
location point
);

CREATE TABLE weather (
city varchar(80) references cities,
temp_lo int,
temp_hi int,
prcp real,
date date
);

test=# \d cities
                Table "cities"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 city     | character varying(80) | not null
 location | point                 |
Primary key: cities_pkey
Triggers: RI_ConstraintTrigger_185133,
          RI_ConstraintTrigger_185135

test=# \d weather
               Table "weather"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 city    | character varying(80) |
 temp_lo | integer               |
 temp_hi | integer               |
 prcp    | real                  |
 date    | date                  |
Triggers: RI_ConstraintTrigger_185131

test=#  INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR:  <unnamed> referential integrity violation - key referenced from weather not found in cities

INSERT INTO cities VALUES ('Berkeley', '0,0');
INSERT 185138 1
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
INSERT 185139 1

Bye
 Rainer Tammer


No file was uploaded with this report