Re: referential integrity violation

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(at)fashaf(dot)co(dot)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(at)fashaf(dot)co(dot)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)
> > > );
> > >