473,327 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 1456
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Paul Mateer | last post by:
Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and...
7
by: George Hester | last post by:
Please take a look at this google artcle: http://groups.google.com/groups?hl=en&lr=&frame=right&th=55d6f4b50f5f9382&seekm=411f370d%241%40olaf.komtel.net#link9 The op was having trouble with...
2
by: Anthony Cuttitta Jr. | last post by:
We have an application that outputs several different graphs from data downloaded from our AS400. The application has worked without (this) issue for several months now, but just recently, the...
0
by: Kevin Spencer | last post by:
Hi all, I am working on a service that uploads METAR weather information to the National Weather Service FTP site. The service I'm authoring is hosted on a Windows 200 server, and the NWS FTP...
2
by: Ben Rush | last post by:
Hello World, Okay, I have spent the day browsing the newsgroups and reading up on article after article concerning ViewState corruption and so forth, and I have a couple questions. We...
5
by: Robert | last post by:
I have a series of web applications (configured as separate applications) on a server. There is a main application at the root and then several virtual directories that are independant...
0
by: Charles Leonard | last post by:
I am having yet another issue with Windows Server 2003. This time, the web service (a file import web service) appears to run except for one odd message: "ActiveX component can't create object". ...
4
by: Paul | last post by:
Hi, I've been struggling with this today, I'm developing a DotNet2.0 website in C# that needs to call a long running data query. Obviously this is a good candidate for an Asynchronous call, so...
1
by: AlekseyUS | last post by:
Hi, I'm a little stuck, I basically need to copy all the information within a specific file in Temp and append it to a file in another location. I'm not having any problems with smaller size...
13
by: SAL | last post by:
Hello, I'm trying to include a popup in the ItemTemplate of a gridview row. The ItemTemplate for the field contains a textbox and when the user clicks in the textbox I want a popup panel to show...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.