Thread: foreign key creation problem

foreign key creation problem

From

"Adam Fisher"

Date:

13 December 2001, 01:59:35

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)

thanks
adam

Re: foreign key creation problem

From

Stephan Szabo

Date:

13 December 2001, 02:59:35

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?

Re: foreign key creation problem

From

Stephan Szabo

Date:

13 December 2001, 04:38:28

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
> > 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?
>
>
>

Re: foreign key creation problem

From

"Adam Fisher"

Date:

13 December 2001, 04:39:40

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?

Money reformatting

From

"Rich Ryan"

Date:

13 December 2001, 07:19:43

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 ?

From

"Serkan BEKTAS"

Date:

13 December 2001, 08:08:29

How Can I reach the mail archieve ?

Serkan

Re: How Can I reach the mail archieve ?

From

"Marc G. Fournier"

Date:

13 December 2001, 15:49:44

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

same archives, just different views on them ...


On Thu, 13 Dec 2001, Serkan BEKTAS wrote:

>
> How Can I reach the mail archieve ?
>
> Serkan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Re: Money reformatting

From

Jason Earl

Date:

13 December 2001, 16:38:57

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

Jason

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

> 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

Re: foreign key creation problem

From

Stephan Szabo

Date:

13 December 2001, 21:29:57

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.

Re: foreign key creation problem

From

adam fisher

Date:

14 December 2001, 11:30:20

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?

Re: foreign key creation problem

From

adam fisher

Date:

14 December 2001, 11:38:46

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
> > 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?
>
>
>

Re: foreign key creation problem

From

adam fisher

Date:

14 December 2001, 11:53:43

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
> > 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?
>
>
>