From: | "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com> |
---|---|
To: | mike <mike(at)bristolreccc(dot)co(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: FK issue |
Date: | 2004-06-10 16:50:26 |
Message-ID: | 200406100950.27053.uwe@oss4u.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thursday 10 June 2004 09:27 am, mike wrote:
> On Thu, 2004-06-10 at 17:03, Uwe C. Schroeder wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Thursday 10 June 2004 08:21 am, mike wrote:
> > > I have set up a FK as follows
> > >
> > > ALTER TABLE lk_sub_con ADD FOREIGN KEY (type) REFERENCES
> > > lk_sort_of_contact(type_code);
> > >
> > > However when I do this
> > >
> > > INSERT INTO lk_sort_of_contact (type_code) VALUES ('1') (ie: a NULL
> > > into the FK) it works
>
> this should have said
>
> INSERT INTO lk_sub_con (sub_cat) VALUES ('1') (ie: a NULL into
> the FK) it works ie: a NULL in type
>
> (wrong copy)
>
> > > Is this a bug?
> >
> > No.
> > you have a table lk_sub_con. That table has the foreign key assigned.
> > This means it doesn't matter what you put into lk_sort_of_contact , it
> > matters what you put into lk_sub_con.
> > The foreign key says something like
> > "If you put a value into field type of table lk_sub_con, the same value
> > must be existant in table lk_sort_of_contact field type_code"
> >
> > Try to add a null value to lk_sub_con.type - or any value that's not in
> > lk_sort_of_contact. Postgres will throw an error.
>
> this is the problem - it doesnt if I put a null in (the refernced column
> has no nulls)
NULL is simply no value. A foreign key only checks for values.
Modify lk_sub_con to have a "NOT NULL" in the definition of type.
NOT NULL forces the field type to have a value and whenever there's a value
the foreign key will kick in.
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFAyJFTjqGXBvRToM4RAkegAJ4tB10hakpKelh8xtPA/aL25vivNwCfdoir
jafAfrwLLx7bwCgAsC8hY2Y=
=faaM
-----END PGP SIGNATURE-----
From | Date | Subject | |
---|---|---|---|
Next Message | Mikhail Terekhov | 2004-06-10 17:11:02 | Re: postgres on SuSE 9.1 |
Previous Message | Stephan Szabo | 2004-06-10 16:47:32 | Re: FK issue |