foreign key creation problem

On Thu, 13 Dec 2001, Adam Fisher wrote:

Hi All,
I am trying to add a foreign key comstraint to an already-populated table
using the ALTER TABLE command. I am linking to the primary key of the master
table, and the slave table also has a primary key, however the field I am
adding the constraint to is not indexed.
When I try and create the constraint using:
alter table inventory
add constraint fk_inv_item_stkNo
foreign key ("stockNo") references "item" ("stockNo")

i get the following message:

UNIQUE constraint matching given keys for referenced table not found "item"

Can anybody tell me what I'm doing wrong? The field stockNo in the item
table is the primary key, so it is unique. Both fields have the dataType
integer and the primary key is also a sequence (i.e auto-incrementing)

Can you send the full schema of the tables involved?

Hi Stephan,
Schemas as requested:

CREATE TABLE "invtest" (
"invid" integer DEFAULT nextval('"invtest_invid_seq"'::text) NOT
NULL,
"orderline" numeric(8,0),
"location" character varying(35),
"sellprice" numeric(7,2),
"stockno" integer,
"label" character(1),
"indate" date,
"unitid" numeric(8,0),
"qty" numeric(5,0) NOT NULL,
"reord" numeric(2,0),
"comno" numeric(14,0),
"taxdue" numeric(7,2),
"taxfree" numeric(7,2),
"conline" numeric(8,0),
"discom" numeric(14,0),
CONSTRAINT "invtest_qty" CHECK ((qty >= '0'::"numeric")),
Constraint "invtest_pkey" Primary Key ("invid"));

CREATE TABLE "item2" (
"stockno" integer DEFAULT nextval('"item2_stockno_seq"'::text) NOT
NULL,
"artist" character varying(40) NOT NULL,
"title" character varying(40) NOT NULL,
"fmt" character(3) NOT NULL,
"country" character varying(6),
"comment" character varying(255),
"apn" character(14),
"catno" character varying(25),
"sup" character(3),
"collectors" character(1) DEFAULT 'n',
"genre" character varying(10),
"shopcom" character varying(50),
Constraint "item2_pkey" Primary Key ("stockno"));

CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER INSERT OR UPDATE ON "item2" FROM "fmt"
NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"
('<unnamed>', 'item2', 'fmt', 'UNSPECIFIED', 'fmt', 'fmt');

CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER INSERT OR UPDATE ON "item2" FROM "genre"
NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"
('<unnamed>', 'item2', 'genre', 'UNSPECIFIED', 'genre', 'gencode');

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, 13 December 2001 3:52
To: Adam Fisher
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] foreign key creation problem

On Thu, 13 Dec 2001, Adam Fisher wrote:

Hi All,
I am trying to add a foreign key comstraint to an already-populated table
using the ALTER TABLE command. I am linking to the primary key of the

master

table, and the slave table also has a primary key, however the field I am
adding the constraint to is not indexed.
When I try and create the constraint using:
alter table inventory
add constraint fk_inv_item_stkNo
foreign key ("stockNo") references "item" ("stockNo")

i get the following message:

UNIQUE constraint matching given keys for referenced table not found

"item"

Can anybody tell me what I'm doing wrong? The field stockNo in the item
table is the primary key, so it is unique. Both fields have the dataType
integer and the primary key is also a sequence (i.e auto-incrementing)

Can you send the full schema of the tables involved?

Hi Stephan,

Schemas as requested:

CREATE TABLE "invtest" (

"invid" integer DEFAULT nextval('"invtest_invid_seq"'::text) NOT

NULL,

"orderline" numeric(8,0),

"location" character varying(35),

"sellprice" numeric(7,2),

"stockno" integer,

"label" character(1),

"indate" date,

"unitid" numeric(8,0),

"qty" numeric(5,0) NOT NULL,

"reord" numeric(2,0),

"comno" numeric(14,0),

"taxdue" numeric(7,2),

"taxfree" numeric(7,2),

"conline" numeric(8,0),

"discom" numeric(14,0),

CONSTRAINT "invtest_qty" CHECK ((qty >= '0'::"numeric")),

Constraint "invtest_pkey" Primary Key ("invid"));

CREATE TABLE "item2" (

"stockno" integer DEFAULT nextval('"item2_stockno_seq"'::text) NOT

NULL,

"artist" character varying(40) NOT NULL,

"title" character varying(40) NOT NULL,

"fmt" character(3) NOT NULL,

"country" character varying(6),

"comment" character varying(255),

"apn" character(14),

"catno" character varying(25),

"sup" character(3),

"collectors" character(1) DEFAULT 'n',

"genre" character varying(10),

"shopcom" character varying(50),

Constraint "item2_pkey" Primary Key ("stockno"));

CREATE CONSTRAINT TRIGGER "<unnamed>"

AFTER INSERT OR UPDATE ON "item2" FROM "fmt"

NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW

EXECUTE PROCEDURE "RI_FKey_check_ins"

('<unnamed>', 'item2', 'fmt', 'UNSPECIFIED', 'fmt', 'fmt');

CREATE CONSTRAINT TRIGGER "<unnamed>"

AFTER INSERT OR UPDATE ON "item2" FROM "genre"

NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW

EXECUTE PROCEDURE "RI_FKey_check_ins"

('<unnamed>', 'item2', 'genre', 'UNSPECIFIED', 'genre', 'gencode');

-----Original Message-----

From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]

Sent: Thursday, 13 December 2001 3:52

To: Adam Fisher

Cc: pgsql-general@postgresql.org

Subject: Re: [GENERAL] foreign key creation problem

On Thu, 13 Dec 2001, Adam Fisher wrote:

Hi All,

I am trying to add a foreign key comstraint to an already-populated table

using the ALTER TABLE command. I am linking to the primary key of the

master

table, and the slave table also has a primary key, however the field I am

adding the constraint to is not indexed.

When I try and create the constraint using:

alter table inventory

add constraint fk_inv_item_stkNo

foreign key ("stockNo") references "item" ("stockNo")

i get the following message:

UNIQUE constraint matching given keys for referenced table not found

"item"

Can anybody tell me what I'm doing wrong? The field stockNo in the item

table is the primary key, so it is unique. Both fields have the dataType

integer and the primary key is also a sequence (i.e auto-incrementing)

Can you send the full schema of the tables involved?

On Thu, 13 Dec 2001, adam fisher wrote:

CREATE TABLE "item2" (
"stockno" integer DEFAULT nextval('"item2_stockno_seq"'::text) NOT
NULL,
"artist" character varying(40) NOT NULL,
"title" character varying(40) NOT NULL,
"fmt" character(3) NOT NULL,
"country" character varying(6),
"comment" character varying(255),
"apn" character(14),
"catno" character varying(25),
"sup" character(3),
"collectors" character(1) DEFAULT 'n',
"genre" character varying(10),
"shopcom" character varying(50),
Constraint "item2_pkey" Primary Key ("stockno"));

I'm assuming item from the statement below and item2 here
are the same structure? It looks like you may have created the table,
possibly with a stockNo, but not in double quotes which would have rolled
the real name of the column to "stockno" which won't match the "stockNo"
below.

Show quoted text

On Thu, 13 Dec 2001, Adam Fisher wrote:

Hi All,
I am trying to add a foreign key comstraint to an already-populated table
using the ALTER TABLE command. I am linking to the primary key of the

master

table, and the slave table also has a primary key, however the field I am
adding the constraint to is not indexed.
When I try and create the constraint using:
alter table inventory
add constraint fk_inv_item_stkNo
foreign key ("stockNo") references "item" ("stockNo")

i get the following message:

UNIQUE constraint matching given keys for referenced table not found

"item"

Can anybody tell me what I'm doing wrong? The field stockNo in the item
table is the primary key, so it is unique. Both fields have the dataType
integer and the primary key is also a sequence (i.e auto-incrementing)

Can you send the full schema of the tables involved?

I have a price field of type 'money'. 90% of the time, the output format of
money $x.xx, is great. But occasionally I need to strip the $ and truncate
the decimal places, i.e. turn it into an int. Is there any easy way to do
this? None of the built-in postgres text formatting functions take the money
type as an arg. I tried type casting, but that didn't work either. I tried
to check the mail list archives, but it's been down for about 2-3 days.
Error I get is
An error occured!
connectDBStart() -- connect() failed: Connection refused Is the postmaster
running (with -i) at 'db.postgresql.org' and accepting connections on TCP/IP
port 5437?

Thanks much,

Rich

How Can I reach the mail archieve ?

Serkan

fts.postgresql.org -or- archives.postgresql.org

same archives, just different views on them ...

On Thu, 13 Dec 2001, Serkan BEKTAS wrote:

Show quoted text

How Can I reach the mail archieve ?

Serkan

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Have you thought about using the numeric type. I believe that they
money type is deprecated.

Jason

"Rich Ryan" <rich@usedcars.com> writes:

Show quoted text

I have a price field of type 'money'. 90% of the time, the output
format of money $x.xx, is great. But occasionally I need to strip
the $ and truncate the decimal places, i.e. turn it into an int. Is
there any easy way to do this? None of the built-in postgres text
formatting functions take the money type as an arg. I tried type
casting, but that didn't work either. I tried to check the mail list
archives, but it's been down for about 2-3 days. Error I get is An
error occured! connectDBStart() -- connect() failed: Connection
refused Is the postmaster running (with -i) at 'db.postgresql.org'
and accepting connections on TCP/IP port 5437?

Thanks much,

Rich

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

I don't believe it!
In my haste to find a greater problem, I ignored case-sensitivity. Thank you
very much for pointing that out, it's working now.

However, can I now ask another question?

The item2 table has about 450,000 lines in it, and the inventory table has
about 89000. I have been running the alter table command to add the foreign
key for about 8 hours now, and it's still going. Is there anything I can do
to speed it up. Would it have helped if the field that the foreign key
constraint is applied to was indexed?

Thanks again,
adam

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, 13 December 2001 5:33
To: adam fisher
Cc: 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] foreign key creation problem

On Thu, 13 Dec 2001, adam fisher wrote:

CREATE TABLE "item2" (
"stockno" integer DEFAULT nextval('"item2_stockno_seq"'::text) NOT
NULL,
"artist" character varying(40) NOT NULL,
"title" character varying(40) NOT NULL,
"fmt" character(3) NOT NULL,
"country" character varying(6),
"comment" character varying(255),
"apn" character(14),
"catno" character varying(25),
"sup" character(3),
"collectors" character(1) DEFAULT 'n',
"genre" character varying(10),
"shopcom" character varying(50),
Constraint "item2_pkey" Primary Key ("stockno"));

I'm assuming item from the statement below and item2 here
are the same structure? It looks like you may have created the table,
possibly with a stockNo, but not in double quotes which would have rolled
the real name of the column to "stockno" which won't match the "stockNo"
below.

On Thu, 13 Dec 2001, Adam Fisher wrote:

Hi All,
I am trying to add a foreign key comstraint to an already-populated

table

using the ALTER TABLE command. I am linking to the primary key of the

master

table, and the slave table also has a primary key, however the field I

am

Show quoted text

adding the constraint to is not indexed.
When I try and create the constraint using:
alter table inventory
add constraint fk_inv_item_stkNo
foreign key ("stockNo") references "item" ("stockNo")

i get the following message:

UNIQUE constraint matching given keys for referenced table not found

"item"

Can anybody tell me what I'm doing wrong? The field stockNo in the item
table is the primary key, so it is unique. Both fields have the dataType
integer and the primary key is also a sequence (i.e auto-incrementing)

Can you send the full schema of the tables involved?

Hi Again,
Scratch that. I was running two seperate proceedures on one of the tables,
so the alter table command was blocked. Once I ran it properly, it only took
about 5 minutes. Sorry about that...
adam

I don't believe it!
In my haste to find a greater problem, I ignored case-sensitivity. Thank you
very much for pointing that out, it's working now.

However, can I now ask another question?

The item2 table has about 450,000 lines in it, and the inventory table has
about 89000. I have been running the alter table command to add the foreign
key for about 8 hours now, and it's still going. Is there anything I can do
to speed it up. Would it have helped if the field that the foreign key
constraint is applied to was indexed?

Thanks again,
adam

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, 13 December 2001 5:33
To: adam fisher
Cc: 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] foreign key creation problem

On Thu, 13 Dec 2001, adam fisher wrote:

CREATE TABLE "item2" (
"stockno" integer DEFAULT nextval('"item2_stockno_seq"'::text) NOT
NULL,
"artist" character varying(40) NOT NULL,
"title" character varying(40) NOT NULL,
"fmt" character(3) NOT NULL,
"country" character varying(6),
"comment" character varying(255),
"apn" character(14),
"catno" character varying(25),
"sup" character(3),
"collectors" character(1) DEFAULT 'n',
"genre" character varying(10),
"shopcom" character varying(50),
Constraint "item2_pkey" Primary Key ("stockno"));

I'm assuming item from the statement below and item2 here
are the same structure? It looks like you may have created the table,
possibly with a stockNo, but not in double quotes which would have rolled
the real name of the column to "stockno" which won't match the "stockNo"
below.

On Thu, 13 Dec 2001, Adam Fisher wrote:

Hi All,
I am trying to add a foreign key comstraint to an already-populated

table

using the ALTER TABLE command. I am linking to the primary key of the

master

table, and the slave table also has a primary key, however the field I

am

Show quoted text

adding the constraint to is not indexed.
When I try and create the constraint using:
alter table inventory
add constraint fk_inv_item_stkNo
foreign key ("stockNo") references "item" ("stockNo")

i get the following message:

UNIQUE constraint matching given keys for referenced table not found

"item"

Can anybody tell me what I'm doing wrong? The field stockNo in the item
table is the primary key, so it is unique. Both fields have the dataType
integer and the primary key is also a sequence (i.e auto-incrementing)

Can you send the full schema of the tables involved?

On Fri, 14 Dec 2001, adam fisher wrote:

Hi Again,
Scratch that. I was running two seperate proceedures on one of the tables,
so the alter table command was blocked. Once I ran it properly, it only took
about 5 minutes. Sorry about that...

No problem. The implementation of doing the check is not the best (see
recent conversation in hackers if you're interested) and can be a bit
slow.