How foreign key info is stored
От | Ken Williams |
---|---|
Тема | How foreign key info is stored |
Дата | |
Msg-id | B3AEA527-710A-11D7-A353-003065F6D85A@mathforum.org обсуждение исходный текст |
Ответы |
Re: How foreign key info is stored
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: "Jeff MacDonald"Дата:
Сообщение: Re: ran an update outside of transaction, can i roll it back in anyway ?