Thread: FK issue

FK issue

From

mike

Date:

10 June 2004, 15:28:26

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

Is this a bug?

Re: FK issue

From

Doug McNaught

Date:

10 June 2004, 15:45:33

mike <mike@bristolreccc.co.uk> writes:

> 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
>
> Is this a bug?

No.

NULLs are permitted in referencing columns unless that column is
declared NOT NULL.

-Doug

Re: FK issue

From

Tom Lane

Date:

10 June 2004, 16:00:59

mike <mike@bristolreccc.co.uk> writes:
> INSERT INTO lk_sort_of_contact (type_code) VALUES ('1') (ie: a NULL into
> the FK) it works

> Is this a bug?

No.  Mark the column NOT NULL if you wish to disallow nulls.

            regards, tom lane

Re: FK issue

From

"Uwe C. Schroeder"

Date:

10 June 2004, 16:03:11

-----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
>
> 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.
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.

    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)

iD8DBQFAyIY4jqGXBvRToM4RArWZAJwLGsUpEwCgPTybCbmkIE52op1fmwCfWrfl
44hCrMlGYIaL8AvTXzeR7VI=
=yMO4
-----END PGP SIGNATURE-----

Re: FK issue

From

mike

Date:

10 June 2004, 16:33:49

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@postgresql.org
>

Re: FK issue

From

Stephan Szabo

Date:

10 June 2004, 16:47:39

On Thu, 10 Jun 2004, mike wrote:

> On Thu, 2004-06-10 at 17:03, Uwe C. Schroeder wrote:
> > 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)

NULLs in a referencing column are a special case in the spec.  For a
single column key, a NULL is considered to pass the constraint. For
multiple column keys it depends on the match type.

Re: FK issue

From

"Uwe C. Schroeder"

Date:

10 June 2004, 16:50:32

-----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-----