Thread: Bug #583: Wrong example in 7.2 Tutorial

Bug #583: Wrong example in 7.2 Tutorial

From
pgsql-bugs@postgresql.org
Date:
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

Re: Bug #583: Wrong example in 7.2 Tutorial

From
Bruce Momjian
Date:
Great.  Patch applied.  Thanks.


---------------------------------------------------------------------------

pgsql-bugs@postgresql.org wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: doc/src/sgml/advanced.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v
retrieving revision 1.26
diff -c -r1.26 advanced.sgml
*** doc/src/sgml/advanced.sgml    2001/11/23 21:08:51    1.26
--- doc/src/sgml/advanced.sgml    2002/02/12 22:22:06
***************
*** 103,114 ****

  <programlisting>
  CREATE TABLE cities (
!     name        varchar(80) primary key,
      location    point
  );

  CREATE TABLE weather (
!     city        varchar(80) references weather,
      temp_lo        int,
      temp_hi        int,
      prcp        real,
--- 103,114 ----

  <programlisting>
  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,