469,111 Members | 1,977 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,111 developers. It's quick & easy.

FK issue

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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
12 1316
mike <mi**@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2
mike <mi**@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3
-----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 haveto
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)

iD8DBQFAyIY4jqGXBvRToM4RArWZAJwLGsUpEwCgPTybCbmkIE 52op1fmwCfWrfl
44hCrMlGYIaL8AvTXzeR7VI=
=yMO4
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4
-----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 haveto
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)

iD8DBQFAyIY4jqGXBvRToM4RArWZAJwLGsUpEwCgPTybCbmkIE 52op1fmwCfWrfl
44hCrMlGYIaL8AvTXzeR7VI=
=yMO4
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #5
mike <mi**@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #6
mike <mi**@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #7
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)

iD8DBQFAyIY4jqGXBvRToM4RArWZAJwLGsUpEwCgPTybCbmkIE 52op1fmwCfWrfl
44hCrMlGYIaL8AvTXzeR7VI=
=yMO4
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #8
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)

iD8DBQFAyIY4jqGXBvRToM4RArWZAJwLGsUpEwCgPTybCbmkIE 52op1fmwCfWrfl
44hCrMlGYIaL8AvTXzeR7VI=
=yMO4
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #9

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.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #10

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.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #11
-----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-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #12
-----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-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by George Hester | last post: by
2 posts views Thread by Anthony Cuttitta Jr. | last post: by
reply views Thread by Kevin Spencer | last post: by
reply views Thread by Charles Leonard | last post: by
4 posts views Thread by Paul | last post: by
1 post views Thread by AlekseyUS | last post: by
13 posts views Thread by SAL | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.