Обсуждение: referential integrity violation
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)
);
Вложения
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
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 thatis 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 istill dont get it :) From what i can tell i may as well make everything NOT NULL and creat a dummy entry to reference tofor 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.
Yes but i am submitting a null value, i submit nothing for slip_printer and i get that error. Same goes for any value thatis 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 istill dont get it :) From what i can tell i may as well make everything NOT NULL and creat a dummy entry to reference tofor 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)
Вложения
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 Fri, 18 Oct 2002 at 10:51:24 -0400, Darren Ferguson wrote: > 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 valuethat 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 referenceto 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
Вложения
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: > Yes but i am submitting a null value, i submit nothing for slip_printer and i get that error. Same goes for any value thatis 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 istill dont get it :) From what i can tell i may as well make everything NOT NULL and creat a dummy entry to reference tofor 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) > > > ); > > >
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);
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