Обсуждение: NOTICE: ignoring incomplete trigger group for constraint
hi !
I have running postgres 8.3.1 and a dump file from postgers 7.2
:-P , but when i tried to restore the dump i have the next
message:
(by the way i made the dump file using pg_dump of postgresql 8.3)
CREATE CONSTRAINT TRIGGER "valida_ent_a_sal" AFTER DELETE ON "ent_a"
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_del" ('valida_ent_a_sal', 'sal_d', 'ent_a', 'FULL',
'tagname_ed', 'tagname_ea');
psql:lostriggers:10: NOTICE: ignoring incomplete trigger group for
constraint "valida_ent_a_sal" FOREIGN KEY sal_d(tagname_ed) REFERENCES
ent_a(tagname_ea)
DETAIL: Found referenced table's DELETE trigger.
CREATE TRIGGER
and this:
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "scenes"
FROM "scenes_sub" INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_upd" ('<unnamed>', 'scenes_sub', 'scenes',
'UNSPECIFIED', 'scene', 'scene', 'tag', 'tag');
psql:lostriggers:965: NOTICE: ignoring incomplete trigger group for
constraint "<unnamed>" FOREIGN KEY scenes_sub(scene,tag)
REFERENCES scenes(scene,tag)
DETAIL: Found referenced table's UPDATE trigger.
CREATE TRIGGER
why some triggers are unnamed???
Which is the best way to solve this???? searching on google
found that i must create foreign keys, but i don't know if is enough
with the creation of the foreign key or if i must create the foreign
key and the constraint too.
there is an 'automagic' way to convert a constraint into a foreign
key ;-)
thanks!
erobles <erobles@sensacd.com.mx> writes:
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
[ Please don't send all-html mail to the lists ]
> I have running postgres 8.3.1 and a dump file from postgers 7.2
> :-P , but when i tried to restore the dump i have the next
> message:
> psql:lostriggers:10: NOTICE: ignoring incomplete trigger group for
> constraint "valida_ent_a_sal" FOREIGN KEY sal_d(tagname_ed) REFERENCES
> ent_a(tagname_ea)
Yeah, this is known to happen in some cases where there was a broken
(incompletely enforced) foreign key constraint in your old database.
The odds are good that what you should do is nothing at all, because
you probably didn't even realize you still had the FK constraint in the
old database: the most common error cases weren't enforced. It's likely
that if you try to add the FK constraint now, you'll find it fails
because the data doesn't even satisfy the constraint. So you could
just leave things alone and the new database will behave approximately
like the old one did. But if you really want to add the FK constraint
back in, ALTER TABLE ADD FOREIGN KEY is the way.
BTW, the known cases for this are follow-on damage from a bug in 7.0
pg_dump. Does the ancestry of this database go back that far?
regards, tom lane
On 05/21/2010 11:18 AM, Tom Lane wrote: > > Yeah, this is known to happen in some cases where there was a broken > (incompletely enforced) foreign key constraint in your old database. > The odds are good that what you should do is nothing at all, because > you probably didn't even realize you still had the FK constraint in the > old database: the most common error cases weren't enforced. It's likely > that if you try to add the FK constraint now, you'll find it fails > because the data doesn't even satisfy the constraint. So you could > just leave things alone and the new database will behave approximately > like the old one did. But if you really want to add the FK constraint > back in, ALTER TABLE ADD FOREIGN KEY is the way. > > hi, i made the alter table to add the foreign key, but in some constraints i have the follow error: ERROR: there is no unique constraint matching given keys for referenced table "table_name'" there is a way to solve this?? what can i do ?? regards, erobles
On 22/05/2010 17:03, erobles wrote: > ERROR: there is no unique constraint matching given keys for referenced > table "table_name'" > > > there is a way to solve this?? what can i do ?? It means you need to have a primary key, or at least a unique constraint, on the target table which uses the column(s) which the foreign key references. For example: postgres=# create table a(f1 integer, f2 integer); CREATE TABLE postgres=# create table b(f3 integer, f4 integer); CREATE TABLE postgres=# alter table a add foreign key (f2) references b(f3); ERROR: there is no unique constraint matching given keys for referenced table "b" If I now add a primary key to table b, it works: postgres=# alter table b add primary key(f3); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "b_pkey" for table "b" ALTER TABLE postgres=# alter table a add foreign key (f2) references b(f3); ALTER TABLE HTH. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie