f



Foreign keys referring table in other database


Hi !

IDS 11.50.FC6

I need to set up constraints between tables in different databases.
According to the documentation the tables used in a foreign key
definition must be in the same database.
I have also tried to use synonyms and views thar refers the table in
the other databas but no success.

One idea is to use ER to replicate the tables in question, another
would be to write triggers that does a check against synonyms.

Any other ideas anybody ?

TIA


Ulf
0
5/6/2010 10:54:00 AM
comp.databases.informix 16083 articles. 0 followers. Post Follow

3 Replies
1024 Views

Similar Articles

[PageSpeed] 39

--_29528b80-cd1d-4bdd-9635-bd292cc1552a_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


You can't do what you want unless you consider using a before insert trigge=
r.
WARNING!=20

While you could in theory do this... you really don't want to do this.
It will kill your performance.

Depending on how static the data that is in the table that you're using as =
your constraint=2C you may want to consider replicating the data to the dat=
abase and the you could use a simple foreign key constraint.

Also do you have a use case for this?=20
I can't think of any where you will want to constrain your data against any=
thing which is outside of your data model. Note again=2C data models do not=
 cross database borders. That's a no no.


> From: ulf.akerberg@gmail.com
> Subject: Foreign keys referring table in other database
> Date: Thu=2C 6 May 2010 03:54:00 -0700
> To: informix-list@iiug.org
>=20
>=20
>=20
> Hi !
>=20
> IDS 11.50.FC6
>=20
> I need to set up constraints between tables in different databases.
> According to the documentation the tables used in a foreign key
> definition must be in the same database.
> I have also tried to use synonyms and views thar refers the table in
> the other databas but no success.
>=20
> One idea is to use ER to replicate the tables in question=2C another
> would be to write triggers that does a check against synonyms.
>=20
> Any other ideas anybody ?
>=20
> TIA
>=20
>=20
> Ulf
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
 		 	   		 =20
_________________________________________________________________
Hotmail is redefining busy with tools for the New Busy. Get more from your =
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID28326::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_2=

--_29528b80-cd1d-4bdd-9635-bd292cc1552a_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
..hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Verdana
}
--></style>
</head>
<body class=3D'hmmessage'>
You can't do what you want unless you consider using a before insert trigge=
r.<br>WARNING! <br><br>While you could in theory do this... you really don'=
t want to do this.<br>It will kill your performance.<br><br>Depending on ho=
w static the data that is in the table that you're using as your constraint=
=2C you may want to consider replicating the data to the database and the y=
ou could use a simple foreign key constraint.<br><br>Also do you have a use=
 case for this? <br>I can't think of any where you will want to constrain y=
our data against anything which is outside of your data model. Note again=
=2C data models do not cross database borders. That's a no no.<br><br><br>&=
gt=3B From: ulf.akerberg@gmail.com<br>&gt=3B Subject: Foreign keys referrin=
g table in other database<br>&gt=3B Date: Thu=2C 6 May 2010 03:54:00 -0700<=
br>&gt=3B To: informix-list@iiug.org<br>&gt=3B <br>&gt=3B <br>&gt=3B <br>&g=
t=3B Hi !<br>&gt=3B <br>&gt=3B IDS 11.50.FC6<br>&gt=3B <br>&gt=3B I need to=
 set up constraints between tables in different databases.<br>&gt=3B Accord=
ing to the documentation the tables used in a foreign key<br>&gt=3B definit=
ion must be in the same database.<br>&gt=3B I have also tried to use synony=
ms and views thar refers the table in<br>&gt=3B the other databas but no su=
ccess.<br>&gt=3B <br>&gt=3B One idea is to use ER to replicate the tables i=
n question=2C another<br>&gt=3B would be to write triggers that does a chec=
k against synonyms.<br>&gt=3B <br>&gt=3B Any other ideas anybody ?<br>&gt=
=3B <br>&gt=3B TIA<br>&gt=3B <br>&gt=3B <br>&gt=3B Ulf<br>&gt=3B __________=
_____________________________________<br>&gt=3B Informix-list mailing list<=
br>&gt=3B Informix-list@iiug.org<br>&gt=3B http://www.iiug.org/mailman/list=
info/informix-list<br> 		 	   		  <br /><hr />Hotmail is redefining busy wi=
th tools for the New Busy. Get more from your inbox. <a href=3D'http://www.=
windowslive.com/campaign/thenewbusy?ocid=3DPID28326::T:WLMTAGL:ON:WL:en-US:=
WM_HMP:042010_2' target=3D'_new'>See how.</a></body>
</html>=

--_29528b80-cd1d-4bdd-9635-bd292cc1552a_--
0
Ian
5/6/2010 11:21:42 AM
--000e0cd32f50fce8f50485ec0080
Content-Type: text/plain; charset=ISO-8859-1

Using ER to replicate the independent table to the local server so you can
constrain against it is the only really viable idea.  Unless you can
replicate it at the application level.


Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference.  Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Thu, May 6, 2010 at 6:54 AM, Ulf <ulf.akerberg@gmail.com> wrote:

>
>
> Hi !
>
> IDS 11.50.FC6
>
> I need to set up constraints between tables in different databases.
> According to the documentation the tables used in a foreign key
> definition must be in the same database.
> I have also tried to use synonyms and views thar refers the table in
> the other databas but no success.
>
> One idea is to use ER to replicate the tables in question, another
> would be to write triggers that does a check against synonyms.
>
> Any other ideas anybody ?
>
> TIA
>
>
> Ulf
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>

--000e0cd32f50fce8f50485ec0080
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Using ER to replicate the independent table to the local server so you can =
constrain against it is the only really viable idea.=A0 Unless you can repl=
icate it at the application level.<br><br><br>Art<br><br clear=3D"all">Art =
S. Kagel<br>

Advanced DataTools (<a href=3D"http://www.advancedatatools.com">www.advance=
datatools.com</a>)<br>IIUG Board of Directors (<a href=3D"mailto:art@iiug.o=
rg">art@iiug.org</a>)<br><br>See you at the 2010 IIUG Informix Conference<b=
r>

April 25-28, 2010<br>Overland Park (Kansas City), KS<br><a href=3D"http://w=
ww.iiug.org/conf">www.iiug.org/conf</a><br><br>Disclaimer: Please keep in m=
ind that my own opinions are my own opinions and do not reflect on my emplo=
yer, Advanced DataTools, the IIUG, nor any other organization with which I =
am associated either explicitly, implicitly, or by inference. =A0Neither do=
 those opinions reflect those of other individuals affiliated with any enti=
ty with which I am affiliated nor those of the entities themselves.<br>

<br>
<br><br><div class=3D"gmail_quote">On Thu, May 6, 2010 at 6:54 AM, Ulf <spa=
n dir=3D"ltr">&lt;<a href=3D"mailto:ulf.akerberg@gmail.com">ulf.akerberg@gm=
ail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D=
"margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padd=
ing-left: 1ex;">

<br>
<br>
Hi !<br>
<br>
IDS 11.50.FC6<br>
<br>
I need to set up constraints between tables in different databases.<br>
According to the documentation the tables used in a foreign key<br>
definition must be in the same database.<br>
I have also tried to use synonyms and views thar refers the table in<br>
the other databas but no success.<br>
<br>
One idea is to use ER to replicate the tables in question, another<br>
would be to write triggers that does a check against synonyms.<br>
<br>
Any other ideas anybody ?<br>
<br>
TIA<br>
<font color=3D"#888888"><br>
<br>
Ulf<br>
_______________________________________________<br>
Informix-list mailing list<br>
<a href=3D"mailto:Informix-list@iiug.org">Informix-list@iiug.org</a><br>
<a href=3D"http://www.iiug.org/mailman/listinfo/informix-list" target=3D"_b=
lank">http://www.iiug.org/mailman/listinfo/informix-list</a><br>
</font></blockquote></div><br>

--000e0cd32f50fce8f50485ec0080--
0
Art
5/6/2010 12:22:42 PM
Ian Michael Gumby wrote:
> You can't do what you want unless you consider using a before insert 
> trigger.
> WARNING!
> 
> While you could in theory do this... you really don't want to do this.
> It will kill your performance.

What he said.

-- 
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.

-- 
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

0
Obnoxio
5/7/2010 4:34:41 PM
Reply: