Using alias as foreign key

Henrik Sorensen

Using alias as foreign key
Dear list,

On DB2 V7 - OS/390.

Sorry for posting the same question twice, but when I posted
this question a while ago, not a single answer came,
so please forgive me for asking the same question twice.

When reading the manuals it seems almost too good to be true.
It should be possible to create a foreign key where the referenced
table is actually a local alias.

The reason for doing this is to prevent that the FK gets dropped when the
real referenced table is dropped.

Pseudo ddl:

create table A . tb1 (code integer, string char(80), primary key(code));
create unique index A.ix1 on A.tb1 ( code );
create alias B. tb1 for A.tb1 ;

create table B.tb2 (a integer) ,
foreign key(a) references B.tb1 ( code ) on delete restrict );


Are there any reasons as to why it should not work ?

And are there any concerns if the structure of the referenced
tables changes ?


Thanks for any hints or comments
Henrik


This e-mail, including attachments, is intended for the person(s) or
company named and may contain confidential and/or legally privileged
information. Unauthorized disclosure, copying or use of this information
may be unlawful and is prohibited. If you are not the intended recipient,
please delete this message and notify the sender

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: Using alias as foreign key
(in response to Henrik Sorensen)
Henrik

Your question implies, that you didn't test it. When I read the manuals, I
do not get the impression, that a foreign key could reference an alias. The
manual says:

> The table name specified after REFERENCES must identify a table that
> exists at the current server

If you try and it don't works, you don't have to bother about the other
questions.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Henrik Sorensen

Re: Using alias as foreign key
(in response to Walter Janißen)
Walter,

Thanks for sharing your insight on the matters.

The sql reference manual under alias and synonyms states:
A table or view can be referred to in an SQL statement by its name,
by an alias that has been defined for its name, or by a synonym that
has been defined for its name.
Thus, aliases and synonyms can be thought of as alternate names
for tables and views.
The option of referencing a table or view by an alias or a synonym
is not explicitly shown in the syntax diagrams ...

Actually I have tried it and the foreign key was indeed created.

But even after carefully reading the manual, it is still not clear to me
what happens to the foreign key definitions using the alias, when the
original table that alias points to is changed.

Maybe it is not common to use alias in foreign key definitions.

That is the reason I ask this estimed list for an advise or any
other practical hints.


Henrik







Walter Janißen
<walter.janissen@V
ICTORIA.DE>

Sent by:
[login to unmask email] To: [login to unmask email]
UGDB2-L.ORG cc: (bcc: Henrik Sorensen/External)
bcc: Henrik Sorensen/External
Subject: Re: Using alias as foreign key
16.12.2004 11:36





Henrik

Your question implies, that you didn't test it. When I read the manuals, I
do not get the impression, that a foreign key could reference an alias. The
manual says:

> The table name specified after REFERENCES must identify a table that
> exists at the current server

If you try and it don't works, you don't have to bother about the other
questions.

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm




This e-mail, including attachments, is intended for the person(s) or
company named and may contain confidential and/or legally privileged
information. Unauthorized disclosure, copying or use of this information
may be unlawful and is prohibited. If you are not the intended recipient,
please delete this message and notify the sender
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Andy Lankester

Re: Using alias as foreign key
(in response to Henrik Sorensen)
I suspect this one of things that no-one has ever thought to try. My advice
would be not to do it since it almost certainly is exercising code paths
that have rarely if ever been tested. What would happen if you tried to
insert a row into a table that had a FK pointing to 'nothing'. I would
suggest that this is 'undefined'. Anyway it should be too difficult to set
up some simple tests and explore what does happen. Relying on it still
happening in Vx is more problematical.

My $0.02

Andy

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Henrik Sorensen
Sent: 16 December 2004 11:27
To: [login to unmask email]
Subject: Re: Using alias as foreign key


Walter,

Thanks for sharing your insight on the matters.

The sql reference manual under alias and synonyms states:
A table or view can be referred to in an SQL statement by its name,
by an alias that has been defined for its name, or by a synonym that
has been defined for its name.
Thus, aliases and synonyms can be thought of as alternate names
for tables and views.
The option of referencing a table or view by an alias or a synonym
is not explicitly shown in the syntax diagrams ...

Actually I have tried it and the foreign key was indeed created.

But even after carefully reading the manual, it is still not clear to me
what happens to the foreign key definitions using the alias, when the
original table that alias points to is changed.

Maybe it is not common to use alias in foreign key definitions.

That is the reason I ask this estimed list for an advise or any other
practical hints.


Henrik







Walter Janißen
<walter.janissen@V
ICTORIA.DE>

Sent by:
[login to unmask email] To:
[login to unmask email]
UGDB2-L.ORG cc:
(bcc: Henrik Sorensen/External)
bcc:
Henrik Sorensen/External
Subject:
Re: Using alias as foreign key
16.12.2004 11:36





Henrik

Your question implies, that you didn't test it. When I read the manuals, I
do not get the impression, that a foreign key could reference an alias. The
manual says:

> The table name specified after REFERENCES must identify a table that
> exists at the current server

If you try and it don't works, you don't have to bother about the other
questions.

----------------------------------------------------------------------------
-----

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm




This e-mail, including attachments, is intended for the person(s) or company
named and may contain confidential and/or legally privileged information.
Unauthorized disclosure, copying or use of this information may be unlawful
and is prohibited. If you are not the intended recipient, please delete this
message and notify the sender
----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.813 / Virus Database: 553 - Release Date: 13/12/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.813 / Virus Database: 553 - Release Date: 13/12/2004


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Andy Lankester

Re: Using alias as foreign key
(in response to Andy Lankester)
I suspect this one of things that no-one has ever thought to try. My advice
would be not to do it since it almost certainly is exercising code paths
that have rarely if ever been tested. What would happen if you tried to
insert a row into a table that had a FK pointing to 'nothing'. I would
suggest that this is 'undefined'. Anyway it should be too difficult to set
up some simple tests and explore what does happen. Relying on it still
happening in Vx is more problematical.

My $0.02

Andy

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Henrik Sorensen
Sent: 16 December 2004 11:27
To: [login to unmask email]
Subject: Re: Using alias as foreign key


Walter,

Thanks for sharing your insight on the matters.

The sql reference manual under alias and synonyms states:
A table or view can be referred to in an SQL statement by its name,
by an alias that has been defined for its name, or by a synonym that
has been defined for its name.
Thus, aliases and synonyms can be thought of as alternate names
for tables and views.
The option of referencing a table or view by an alias or a synonym
is not explicitly shown in the syntax diagrams ...

Actually I have tried it and the foreign key was indeed created.

But even after carefully reading the manual, it is still not clear to me
what happens to the foreign key definitions using the alias, when the
original table that alias points to is changed.

Maybe it is not common to use alias in foreign key definitions.

That is the reason I ask this estimed list for an advise or any other
practical hints.


Henrik







Walter Janißen
<walter.janissen@V
ICTORIA.DE>

Sent by:
[login to unmask email] To:
[login to unmask email]
UGDB2-L.ORG cc:
(bcc: Henrik Sorensen/External)
bcc:
Henrik Sorensen/External
Subject:
Re: Using alias as foreign key
16.12.2004 11:36





Henrik

Your question implies, that you didn't test it. When I read the manuals, I
do not get the impression, that a foreign key could reference an alias. The
manual says:

> The table name specified after REFERENCES must identify a table that
> exists at the current server

If you try and it don't works, you don't have to bother about the other
questions.

----------------------------------------------------------------------------
-----

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm




This e-mail, including attachments, is intended for the person(s) or company
named and may contain confidential and/or legally privileged information.
Unauthorized disclosure, copying or use of this information may be unlawful
and is prohibited. If you are not the intended recipient, please delete this
message and notify the sender
----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.813 / Virus Database: 553 - Release Date: 13/12/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.813 / Virus Database: 553 - Release Date: 13/12/2004


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm