Foreign keys with values of zero rather than null

This is a multi-part message in MIME format.

------_=_NextPart_001_01CA8015.67AEA3C6
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I map data from rows in tables to objects in Java. When I map integers
in the database I map them to integer primitives in Java. The challenge
I am running into is what do I do when a table has a integer foreign key
that is null. Ideally when a table in PostgreSql has a foreign key that
does not exist I would like the value to be zero rather than null. This
works really well for me because I NEVER use a key with the value of 0.



Is there some way for me to tell PostgreSql that a zero is the same as
null when using foreign keys that map to integers? That way I can still
use cascade delete. The only way for me to get around this issue is to
remove the foreign key constraint for cascade delete.



Any help would be appreciated.



Thanks,



Lance Campbell

Software Architect/DBA/Project Manager

Web Services at Public Affairs

217-333-0382




------_=_NextPart_001_01CA8015.67AEA3C6
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" =
xmlns:p=3D"urn:schemas-microsoft-com:office:powerpoint" =
xmlns:a=3D"urn:schemas-microsoft-com:office:access" =
xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" =
xmlns:s=3D"uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" =
xmlns:rs=3D"urn:schemas-microsoft-com:rowset" xmlns:z=3D"#RowsetSchema" =
xmlns:b=3D"urn:schemas-microsoft-com:office:publisher" =
xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadsheet" =
xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" =
xmlns:odc=3D"urn:schemas-microsoft-com:office:odc" =
xmlns:oa=3D"urn:schemas-microsoft-com:office:activation" =
xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" =
xmlns:rtc=3D"http://microsoft.com/officenet/conferencing" =
xmlns:D=3D"DAV:" xmlns:Repl=3D"http://schemas.microsoft.com/repl/" =
xmlns:mt=3D"http://schemas.microsoft.com/sharepoint/soap/mee tings/" =
xmlns:x2=3D"http://schemas.microsoft.com/office/excel/2003/x ml" =
xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" =
xmlns:ois=3D"http://schemas.microsoft.com/sharepoint/soap/oi s/" =
xmlns:dir=3D"http://schemas.microsoft.com/sharepoint/soap/di rectory/" =
xmlns:ds=3D"http://www.w3.org/2000/09/xmldsig#" =
xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint/dsp" =
xmlns:udc=3D"http://schemas.microsoft.com/data/udc" =
xmlns:xsd=3D"http://www.w3.org/2001/XMLSchema" =
xmlns:sub=3D"http://schemas.microsoft.com/sharepoint/soap/20 02/1/alerts/"=
xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#" =
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" =
xmlns:sps=3D"http://schemas.microsoft.com/sharepoint/soap/" =
xmlns:xsi=3D"http://www.w3.org/2001/XMLSchema-instance" =
xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/soap" =
xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " =
xmlns:udcp2p=3D"http://schemas.microsoft.com/data/udc/partto part" =
xmlns:wf=3D"http://schemas.microsoft.com/sharepoint/soap/wor kflow/" =
xmlns:dsss=3D"http://schemas.microsoft.com/office/2006/digsi g-setup" =
xmlns:dssi=3D"http://schemas.microsoft.com/office/2006/digsi g" =
xmlns:mdssi=3D"http://schemas.openxmlformats.org/package/200 6/digital-sig=
nature" =
xmlns:mver=3D"http://schemas.openxmlformats.org/markup-compa tibility/2006=
" xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns:mrels=3D"http://schemas.openxmlformats.org/package/200 6/relationshi=
ps" xmlns:spwp=3D"http://microsoft.com/sharepoint/webpartpages" =
xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/2006/types"=
=
xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/2006/messag=
es" =
xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ SlideLibrary/=
" =
xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor talServer/Pub=
lishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" =
xmlns:st=3D"" xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
[at] font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
[at] font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
..MsoChpDefault
{mso-style-type:export-only;}
[at] page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>I map data from rows in tables to objects in Java. =
When I
map integers in the database I map them to integer primitives in =
Java. 
The challenge I am running into is what do I do when a table has a =
integer
foreign key that is null.  Ideally when a table in PostgreSql has a
foreign key that does not exist I would like the value to be zero rather =
than
null.  This works really well for me because I NEVER use a key with =
the
value of 0.<o:p></o:p></p>

<p class=3DMsoNormal><o:p> </o:p></p>

<p class=3DMsoNormal>Is there some way for me to tell PostgreSql that a =
zero is
the same as null when using foreign keys that map to integers?  =
That way I
can still use cascade delete.  The only way for me to get around =
this
issue is to remove the foreign key constraint for cascade =
delete.<o:p></o:p></p>

<p class=3DMsoNormal><o:p> </o:p></p>

<p class=3DMsoNormal>Any help would be appreciated.<o:p></o:p></p>

<p class=3DMsoNormal><o:p> </o:p></p>

<p class=3DMsoNormal>Thanks,<o:p></o:p></p>

<p class=3DMsoNormal><o:p> </o:p></p>

<p class=3DMsoNormal>Lance Campbell<o:p></o:p></p>

<p class=3DMsoNormal>Software Architect/DBA/Project =
Manager<o:p></o:p></p>

<p class=3DMsoNormal>Web Services at Public Affairs<o:p></o:p></p>

<p class=3DMsoNormal>217-333-0382<o:p></o:p></p>

<p class=3DMsoNormal><o:p> </o:p></p>

</div>

</body>

</html>

------_=_NextPart_001_01CA8015.67AEA3C6--
Lance [ Fr, 18 Dezember 2009 20:07 ] [ ID #2026441 ]

Re: Foreign keys with values of zero rather than null

"Campbell, Lance" <lance [at] illinois.edu> wrote:
> I map data from rows in tables to objects in Java. When I map
> integers in the database I map them to integer primitives in Java.
> The challenge I am running into is what do I do when a table has a
> integer foreign key that is null. Ideally when a table in
> PostgreSql has a foreign key that does not exist I would like the
> value to be zero rather than null. This works really well for me
> because I NEVER use a key with the value of 0.

I would definitely use NULL in the database to indicate that a value
is unknown or not applicable. Really. Always.

How you map that to your Java objects is another issue -- if you
used an Integer object, you would have a natural mapping of NULL in
the database to a Java null and back again. That's what I normally
do. In my view, you need a really good reason to map to the
primitive, especially since Java has added the automatic boxing and
unboxing features.

If you feel compelled to micro-optimize to primitives, you need to
have some way to map the NULLs to your magic number and back again.
The former is pretty easy if you use zero as your magic number,
since getInt returns zero for a NULL, leaving it to you to check
wasNull to determine whether it's really a NULL or a zero, that
direction is easy.

http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.htm l#getInt(int)

http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.htm l#wasNull()

Going the other direction, you need some way to convert a zero in
that column to a NULL in the database. Exactly how you do that
depends on how you are doing your mapping -- unless you want to try
to use PostgreSQL rules to rewrite your statements or some such.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Kevin Grittner [ Fr, 18 Dezember 2009 20:46 ] [ ID #2026443 ]
Datenbanken » gmane.comp.db.postgresql.admin » Foreign keys with values of zero rather than null

Vorheriges Thema: Backslashes in bytea values
Nächstes Thema: Problems to connect to a postgreSQL 8.4 database on windows 2003

[ Startseite ] [ Administrator ] [ Suche ] [ Hinweise ] [ Impressum ]

Powered by FudForum