Thread: Foreign key constraint
Hi,
I have a question. I tried adding a Foreign Key constraint to a table via the ALTER TABLE command. But the records in the table did not match with those in the Parent table. Now my Alter table command, instead of giving me an error of kind - 'records do not match', has gone into a kind of Deadlock between the 2 tables. I am not able to select from the tables, nor delete them, nor drop them. If I try to do any of these, I get the error saying -
" Error : RelationBuildTriggers: 2 record(s) not found for rel invoice"
How do I overcome this error. Any ideas will be greatly appreciated.
Thanks a lot in advance.
Shiva.
Recovery try use: pg_dump -t tbname -f outfile dbname use vi erase f key from outfile, drop old table, reload them. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com
> Hi,
How can I use select ... for update to update limit to update what I
select??
somewhat like:
select url,id from mytable for update order by priority,id limit 5;
I want update the id in above return like:
update mytable set allocatedto='whatever' where id in above return set.
Could I do it in one stmt.
And what is class_name in following:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] expression [ AS name ] [, ...] [ INTO [ TEMPORARY | TEMP ]
[TABLE ] new_table ] [ FROM table [ alias ] [, ...] ] [ WHERE condition ] [ GROUP BY column [, ...] ] [ HAVING
condition[, ...] ] [ { UNION [ ALL ] | INTERSECT | EXCEPT } select ] [ ORDER BY column [ ASC | DESC | USING
operator] [, ...] ] [ FOR UPDATE [ OF class_name [, ...] ] ] LIMIT { count | ALL } [ { OFFSET | , } start ]
can any one give me a example??
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
> > Hi,
>
> How can I use select ... for update to update limit to update what I
> select??
First thing - the SELECT FOR UPDATE is not merge of SELECT and UPDATE
but transaction option. The PostgreSQL use row-locking for UPDATEed rows.
Standard SELECT ignore this lock, but SELECT FOR UPDATE wait until
*other* transaction with UPDATE will commited.
> somewhat like:
> select url,id from mytable for update order by priority,id limit 5; ^^^^^^^^^^^^^^^^^^^see
theSELECT's syntax, ORDER BY must be before FOR UPDATE.
> I want update the id in above return like:
> update mytable set allocatedto='whatever' where id in above return set.
Can't you in UPDATE's WHERE define some 'id' as in above SELECT?
An example (via subselect):
UPDATE mytable SET allocatedto='whatever' WHERE id IN ( SELECT id FROM mytable ORDER BY priority,id LIMIT 5);
But it not will too much fast... better is define relevant 'id'
inside UPDATE's WHERE without sub-select, but if you need define it via
ORDER+LIMIT it's impossible.
Karel