Hi,
I'm wondering some things about how foreign key info is stored.
1) In postgres 7.1.3, how can I access the foreign key relationships
among tables? The pg_constraint table from version 7.3 doesn't seem to
exist, and I couldn't find an equivalent in the documentation for 7.1.
2) Is foreign key definition working properly via 'ALTER TABLE <table>
ADD ...' in 7.3.2? I did the following in a test database (extra
spaces added for clarity):
========================================================================
===
| test=> create table foo (
| test(> id int primary key not null,
| test(> foo_value varchar(30) );
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey'
| for table 'foo'
| CREATE TABLE
|
| test=> create table foo_people (
| test(> person_id integer primary key,
| test(> name varchar(30) );
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
| 'foo_people_pkey' for table 'foo_people'
| CREATE TABLE
|
| test=> alter table foo add foo_person integer references
foo_people(person_id);
| NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
| ALTER TABLE
|
| test=> \d foo
| Table "public.foo"
| Column | Type | Modifiers
| ------------+-----------------------+-----------
| id | integer | not null
| foo_value | character varying(30) |
| foo_person | integer |
| Indexes: foo_pkey primary key btree (id)
========================================================================
===
This indicates to me that the "references foo_people(person_id)" had no
effect. To confirm this, I tried a bogus INSERT:
========================================================================
===
| test=> insert into foo_people (person_id, name) values (1, 'Bozo');
| INSERT 16985 1
|
| test=> insert into foo (id, foo_value, foo_person) values (1, 'foo!',
5);
| INSERT 16986 1
========================================================================
===
That second INSERT should have failed because there's nothing in
foo_people with an id of 5.
Next I tried specifying the constraint a different way. Note that the
first ALTER TABLE command fails because integrity isn't satisfied.
When I delete data from foo I can create the constraint:
========================================================================
===
| test=> alter table foo add foreign key (foo_person) references
foo_people (person_id);
| NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
| NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
| ERROR: $1 referential integrity violation - key referenced from foo
| not found in foo_people
| ERROR: $1 referential integrity violation - key referenced from foo
| not found in foo_people
|
| test=> delete from foo;
| DELETE 1
|
| test=> alter table foo add foreign key (foo_person) references
foo_people (person_id);
| NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
| NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
| ALTER TABLE
|
| test=> \d foo
| Table "public.foo"
| Column | Type | Modifiers
| ------------+-----------------------+-----------
| id | integer | not null
| foo_value | character varying(30) |
| foo_person | integer |
| Indexes: foo_pkey primary key btree (id)
| Foreign Key constraints: $1 FOREIGN KEY (foo_person) REFERENCES
| foo_people(person_id) ON UPDATE NO ACTION ON DELETE NO ACTION
========================================================================
===
And now, notice the foreign key info shows up in "\d foo".
-Ken
