Re: FK issue

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)

> If you don't want nulls in the referenced table lk_sort_of_contact you have to
> define that there. Maybe you make the field type_code unique or the primary
> key.
>

It is
> UC

For info

SELECT type_code FROM lk_sort_of_contact; type_code
----------- 3 6 5 1
2
4
7
9
8
10
11
12
13
14
16
17
20
21

SELECT * FROM lk_sub_con;
sub_id | type | sub_cat | scat_desc
--------+------+---------+-----------
1 | | 1 |
2 | | 20 |
3 | 21 | 22 |

(after the above FK & INSERT)

- --
> 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)
>
> iD8DBQFAyIY4jqGXBvRToM4RArWZAJwLGsUpEwCgPTybCbmkIE52op1fmwCfWrfl
> 44hCrMlGYIaL8AvTXzeR7VI=
> =yMO4
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>