referential integrity violation

What is happening is that you are trying to insert and id for slip
printer into the TABLE pc but the id does not exist in the slip printer
table.

Since you have slip_printer in pc table and you specified that it
references the slip_printer table id then you must have an id in slip
printer for it to insert into the pc table unless you insert a null value
since you did not specify the constraint on the table

HTH

On Fri, 18 Oct 2002 mk@fashaf.co.za wrote:

Hi guys

I have created a Pg DB, Ive included 2 of the tables.
When inserting non NULL values everything works just fine,
but when i want to leave a table empty i get the following error:

EXEC: ERROR: referential integrity violation - key referenced from pc not found in slip_printer

Im recieving the information from a Perl CGI form that is filled in and then INSERT it using perl DBI

If any more information is needed please let me know. As far as i know i should be allowed to have null.

TIA
Merritt

CREATE TABLE slip_printer (
id SERIAL PRIMARY KEY,
make VARCHAR(256) NOT NULL,
model VARCHAR(256) NOT NULL,
UNIQUE (make, model)
);

CREATE TABLE pc (
id SERIAL PRIMARY KEY,
store INTEGER REFERENCES store (id) NOT NULL,
cpu_type INTEGER REFERENCES cpu_type (id) NOT NULL,
cpu_mhz INTEGER REFERENCES cpu_mhz (id) NOT NULL,
memory_type INTEGER REFERENCES memory_type (id) NOT NULL,
memory_amount INTEGER, /* amount of chips/sticks, not size in MB */
hdd INTEGER REFERENCES hdd (id),
hdd_serial VARCHAR(256),
monitor INTEGER REFERENCES monitor (id) NOT NULL,
slip_printer INTEGER REFERENCES slip_printer (id),
report_printer INTEGER REFERENCES report_printer (id),
cash_drawer INTEGER REFERENCES cash_drawer (id) NOT NULL,
scanner INTEGER REFERENCES scanner (id) NOT NULL,
ups INTEGER REFERENCES ups (id),
modem INTEGER REFERENCES modem (id)
);

--
Darren Ferguson

Yes but i am submitting a null value, i submit nothing for slip_printer and i get that error. Same goes for any value that is not restrcited by NOT NULL, the reason i dont restrict them to null is that i want it to be able to be empty. So i still dont get it :) From what i can tell i may as well make everything NOT NULL and creat a dummy entry to reference to for NULL values.

hope that makes sense :)

Merritt

When you say you are submitting a null value are submitting it as '' or
NULL??? If '' then this is not null but an empty string.

If you submit a NULL and the field allows NULL's then you will not see
this error.

HTH

On Fri, 18 Oct 2002 mk@fashaf.co.za wrote:

Yes but i am submitting a null value, i submit nothing for slip_printer and i get that error. Same goes for any value that is not restrcited by NOT NULL, the reason i dont restrict them to null is that i want it to be able to be empty. So i still dont get it :) From what i can tell i may as well make everything NOT NULL and creat a dummy entry to reference to for NULL values.

hope that makes sense :)

Merritt

On Fri, 18 Oct 2002 at 10:34:31 -0400, Darren Ferguson wrote:

What is happening is that you are trying to insert and id for slip
printer into the TABLE pc but the id does not exist in the slip printer
table.

Since you have slip_printer in pc table and you specified that it
references the slip_printer table id then you must have an id in slip
printer for it to insert into the pc table unless you insert a null value
since you did not specify the constraint on the table

HTH

On Fri, 18 Oct 2002 mk@fashaf.co.za wrote:

Hi guys

I have created a Pg DB, Ive included 2 of the tables.
When inserting non NULL values everything works just fine,
but when i want to leave a table empty i get the following error:

EXEC: ERROR: referential integrity violation - key referenced from pc not found in slip_printer

Im recieving the information from a Perl CGI form that is filled in and then INSERT it using perl DBI

If any more information is needed please let me know. As far as i know i should be allowed to have null.

TIA
Merritt

CREATE TABLE slip_printer (
id SERIAL PRIMARY KEY,
make VARCHAR(256) NOT NULL,
model VARCHAR(256) NOT NULL,
UNIQUE (make, model)
);

CREATE TABLE pc (
id SERIAL PRIMARY KEY,
store INTEGER REFERENCES store (id) NOT NULL,
cpu_type INTEGER REFERENCES cpu_type (id) NOT NULL,
cpu_mhz INTEGER REFERENCES cpu_mhz (id) NOT NULL,
memory_type INTEGER REFERENCES memory_type (id) NOT NULL,
memory_amount INTEGER, /* amount of chips/sticks, not size in MB */
hdd INTEGER REFERENCES hdd (id),
hdd_serial VARCHAR(256),
monitor INTEGER REFERENCES monitor (id) NOT NULL,
slip_printer INTEGER REFERENCES slip_printer (id),
report_printer INTEGER REFERENCES report_printer (id),
cash_drawer INTEGER REFERENCES cash_drawer (id) NOT NULL,
scanner INTEGER REFERENCES scanner (id) NOT NULL,
ups INTEGER REFERENCES ups (id),
modem INTEGER REFERENCES modem (id)
);

--
Darren Ferguson

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Darren Ferguson

On Fri, 18 Oct 2002 mk@fashaf.co.za wrote:

Hi guys

I have created a Pg DB, Ive included 2 of the tables.
When inserting non NULL values everything works just fine,
but when i want to leave a table empty i get the following error:

EXEC: ERROR: referential integrity violation - key referenced from pc not found in slip_printer

Im recieving the information from a Perl CGI form that is filled in and then INSERT it using perl DBI

If any more information is needed please let me know. As far as i know i should be allowed to have null.

What is the actual insert statement giving the error (if you need
to you can turn on query logging to see)? NULLs should work fine
but I'd need a test statement to check.

I have tried '' , undef , 'NULL' , 'null' non of them seem to work?

any idea how to submit a NULL value with perl?

Thanks for the help sofar

Merritt

On Mon, Oct 21, 2002 at 10:27:29AM +0200, mk@fashaf.co.za wrote:

I have tried '' , undef , 'NULL' , 'null' non of them seem to work?

any idea how to submit a NULL value with perl?

from the DBI man page:

Null Values

Undefined values, or "undef", can be used to indicate null
values. However, care must be taken in the particular
case of trying to use null values to qualify a "SELECT"
statement. Consider:

SELECT description FROM products WHERE product_code = ?

Binding an "undef" (NULL) to the placeholder will not
select rows which have a NULL "product_code"! Refer to the
SQL manual for your database engine or any SQL book for
the reasons for this. To explicitly select NULLs you have
to say ""WHERE product_code IS NULL"" and to make that
general you have to say:

... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL))

and bind the same value to both placeholders.

So, the answer is to bind undef. If it's not, please go into more detail of how you are using this...

Mike

test=# select * from testme where i is null;
i | a | b
---+---+---
(0 rows)

test=# create unique index uniqi on testme (i);
CREATE
test=# create table reftestme (
test(# i int,
test(# a int references testme (i)
test(# );
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
test=# insert into reftestme values (1, null);
INSERT 1359552 1
test=#

Where testme has: i int, a int, b int

Are you sure you're using null and not a value?

--
Nigel J. Andrews

On Fri, 18 Oct 2002 mk@fashaf.co.za wrote:

On Mon, 21 Oct 2002 mk@fashaf.co.za wrote:

I have tried '' , undef , 'NULL' , 'null' non of them seem to work?

any idea how to submit a NULL value with perl?

Okay, let's take a step back to view the slightly larger picture rather than
the little detail...

With a design such as:

create table t1 (
i int primary key
);

create table t2 (
i int primary key,
t text,
a int references t1 (a)
);

your perl code should be submitting a string such as, note the lack of quotes
around the null:

insert into mytable values ( 23, 'my text', NULL )

i.e.

use Pg;

# connect to server etc.

$conn->exec("insert into mytable values ( 23, 'my text', NULL )");

# error check etc.

Hope this helps.

On Fri, 18 Oct 2002 at 10:34:31 -0400, Darren Ferguson wrote:

What is happening is that you are trying to insert and id for slip
printer into the TABLE pc but the id does not exist in the slip printer
table.

Since you have slip_printer in pc table and you specified that it
references the slip_printer table id then you must have an id in slip
printer for it to insert into the pc table unless you insert a null value
since you did not specify the constraint on the table

HTH

On Fri, 18 Oct 2002 mk@fashaf.co.za wrote:

Hi guys

I have created a Pg DB, Ive included 2 of the tables.
When inserting non NULL values everything works just fine,
but when i want to leave a table empty i get the following error:

EXEC: ERROR: referential integrity violation - key referenced from pc not found in slip_printer

Im recieving the information from a Perl CGI form that is filled in and then INSERT it using perl DBI

If any more information is needed please let me know. As far as i know i should be allowed to have null.

TIA
Merritt

CREATE TABLE slip_printer (
id SERIAL PRIMARY KEY,
make VARCHAR(256) NOT NULL,
model VARCHAR(256) NOT NULL,
UNIQUE (make, model)
);

CREATE TABLE pc (
id SERIAL PRIMARY KEY,
store INTEGER REFERENCES store (id) NOT NULL,
cpu_type INTEGER REFERENCES cpu_type (id) NOT NULL,
cpu_mhz INTEGER REFERENCES cpu_mhz (id) NOT NULL,
memory_type INTEGER REFERENCES memory_type (id) NOT NULL,
memory_amount INTEGER, /* amount of chips/sticks, not size in MB */
hdd INTEGER REFERENCES hdd (id),
hdd_serial VARCHAR(256),
monitor INTEGER REFERENCES monitor (id) NOT NULL,
slip_printer INTEGER REFERENCES slip_printer (id),
report_printer INTEGER REFERENCES report_printer (id),
cash_drawer INTEGER REFERENCES cash_drawer (id) NOT NULL,
scanner INTEGER REFERENCES scanner (id) NOT NULL,
ups INTEGER REFERENCES ups (id),
modem INTEGER REFERENCES modem (id)
);

--
Nigel J. Andrews

In article <Pine.LNX.4.21.0210211547390.3016-100000@ponder.fairway2k.co.uk>,
nandrews@investsystems.co.uk ("Nigel J. Andrews") writes:

On Mon, 21 Oct 2002 mk@fashaf.co.za wrote:

I have tried '' , undef , 'NULL' , 'null' non of them seem to work?

any idea how to submit a NULL value with perl?

Okay, let's take a step back to view the slightly larger picture rather than
the little detail...

With a design such as:

create table t1 (
i int primary key
);

create table t2 (
i int primary key,
t text,
a int references t1 (a)
);

your perl code should be submitting a string such as, note the lack of quotes
around the null:

insert into mytable values ( 23, 'my text', NULL )

i.e.

use Pg;

# connect to server etc.

$conn->exec("insert into mytable values ( 23, 'my text', NULL )");

# error check etc.

You could also let Perl do the quoting for you:

use DBI;

# connect to server etc.

$conn->do (q{
INSERT INTO mytable VALUES (?, ?, ?)
}, undef, 23, "my text", undef);