Postgres Pro
Downloads
Home   >   mailing lists

Thread: problem with foreign key

problem with foreign key

From
Ljubomir Erdeljan
Date:
   hi group,

trying to create foreign key:

ALTER TABLE porder1 ADD CONSTRAINT order_exist
FOREIGN KEY (docuuserid) REFERENCES trade(tradeid) ON DELETE RESTRICT;

column docuuserid is integer in porder1
column tradeid    is serial  in trade and belong to primary key (single
column)

getting In EMS PostgreSQL
ERROR:  insert or update on table "porder1" violates foreign key
constraint "order_exist"
DETAIL: Key (docuuserid)=(8415) is not present in table "trade".

In PostgreSQL  Code Factory
SQL Error: ERROR:  insert or update on table "porder1" violates foreign
key constraint "order_exist"
DETAIL:    Key (docuuserid)=(8415) is not present in table "trade".

postgresql 8.4.4-1 on windows 7 ultimate 64 bit standard installation

I'm not able to figure out what should be a problem.



kindly regards

ljubomir erdeljan

Re: problem with foreign key

From
Szymon Guz
Date:
On 5 September 2010 22:17, Ljubomir Erdeljan <ljubomir.erdeljan@chello.at> wrote:
 hi group,

trying to create foreign key:

ALTER TABLE porder1 ADD CONSTRAINT order_exist
FOREIGN KEY (docuuserid) REFERENCES trade(tradeid) ON DELETE RESTRICT;

column docuuserid is integer in porder1
column tradeid    is serial  in trade and belong to primary key (single column)

getting In EMS PostgreSQL
ERROR:  insert or update on table "porder1" violates foreign key constraint "order_exist"
DETAIL: Key (docuuserid)=(8415) is not present in table "trade".

In PostgreSQL  Code Factory
SQL Error: ERROR:  insert or update on table "porder1" violates foreign key constraint "order_exist"
DETAIL:    Key (docuuserid)=(8415) is not present in table "trade".

postgresql 8.4.4-1 on windows 7 ultimate 64 bit standard installation

I'm not able to figure out what should be a problem.


During the foreign key creation, database checks if all rows are correct, and they are not. There is a row in the porder1 table where field docuuserid = 8415 and there is no row in the table trader where tradeid=8415.

regards
Szymon Guz

Re: problem with foreign key

From
Bèrto ëd Sèra
Date:
An immediate question: did you check that Key (docuuserid)=(8415) is actually present in table "trade"?

Bèrto

On 5 September 2010 23:17, Ljubomir Erdeljan <ljubomir.erdeljan@chello.at> wrote:
 hi group,

trying to create foreign key:

ALTER TABLE porder1 ADD CONSTRAINT order_exist
FOREIGN KEY (docuuserid) REFERENCES trade(tradeid) ON DELETE RESTRICT;

column docuuserid is integer in porder1
column tradeid    is serial  in trade and belong to primary key (single column)

getting In EMS PostgreSQL
ERROR:  insert or update on table "porder1" violates foreign key constraint "order_exist"
DETAIL: Key (docuuserid)=(8415) is not present in table "trade".

In PostgreSQL  Code Factory
SQL Error: ERROR:  insert or update on table "porder1" violates foreign key constraint "order_exist"
DETAIL:    Key (docuuserid)=(8415) is not present in table "trade".

postgresql 8.4.4-1 on windows 7 ultimate 64 bit standard installation

I'm not able to figure out what should be a problem.



kindly regards

ljubomir erdeljan

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.

Re: problem with foreign key

From
Ljubomir Erdeljan
Date:
This relate to different column names
docuuserid is column in porder1 but relate to tradeid in trade.

Thank you,

On 05/09/2010 22:40, Bèrto ëd Sèra wrote:
An immediate question: did you check that  Key (docuuserid)=(8415) is actually present in table "trade"?

Bèrto

On 5 September 2010 23:17, Ljubomir Erdeljan <ljubomir.erdeljan@chello.at> wrote:
 hi group,

trying to create foreign key:

ALTER TABLE porder1 ADD CONSTRAINT order_exist
FOREIGN KEY (docuuserid) REFERENCES trade(tradeid) ON DELETE RESTRICT;

column docuuserid is integer in porder1
column tradeid    is serial  in trade and belong to primary key (single column)

getting In EMS PostgreSQL
ERROR:  insert or update on table "porder1" violates foreign key constraint "order_exist"
DETAIL: Key (docuuserid)=(8415) is not present in table "trade".

In PostgreSQL  Code Factory
SQL Error: ERROR:  insert or update on table "porder1" violates foreign key constraint "order_exist"
DETAIL:    Key (docuuserid)=(8415) is not present in table "trade".

postgresql 8.4.4-1 on windows 7 ultimate 64 bit standard installation

I'm not able to figure out what should be a problem.



kindly regards

ljubomir erdeljan

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



--
==============================
Constitution du 24 juin 1793 - Article 35. - Quand le gouvernement viole les droits du peuple, l'insurrection est, pour le peuple et pour chaque portion du peuple, le plus sacré des droits et le plus indispensable des devoirs.

Re: problem with foreign key

From
Lew
Date:
Please do not top-post.

Bèrto ëd Sèra wrote:
>> An immediate question: did you check that Key (docuuserid)=(8415) is
>> actually present in table "trade"?

Ljubomir Erdeljan wrote:
 >   This relate to different column names
 > docuuserid is column in porder1 but relate to tradeid in trade.

You missed the point.  Did you check that the key is present in "trade"?

IOW, is there a row in "trade" with "tradeid" equal to 8415?  If not, you
cannot create the foreign key because the item in "porder1" cannot point to it.

--
Lew