Re: [firebird-support] Foreign key different field type

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>Why should it be forbidden?  Not everything that's dumb is disallowed. 

Because it can cause problem if someone do this not intentionally.
Also if your system must work with many databases it is then not compatibile. 
(e.g. not compatibile with MSSQL)

regards,
Karol Bieniaszewski


From: Ann Harrison aharri...@ibphoenix.com [firebird-support] 
Sent: Tuesday, June 20, 2017 4:46 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Foreign key different field type

  

On Mon, Jun 19, 2017 at 1:21 PM, Thomas Steinmaurer t...@iblogmanager.com 
[firebird-support] <firebird-support@yahoogroups.com> wrote:

  ,
  >
  > Can someone show me example when it is usefull to have different field
  > type in [referenced and referencing keys of a foreign key relationship]?
  >
  >
  > Why this is not forbidden?


Why should it be forbidden?  Not everything that's dumb is disallowed. 

  Perhaps cause the referenced column in table test1 can only store a
  subset (SMALLINT) of the value range of the INTEGER used in test2?

  Even if this works at DDL time, I wonder how strict the optimizer then
  is in JOIN statements using an index. Haven't tried.


Nor have I tried, but Firebird uses the same key representation for most numeric
columns, so having mixed sizes of numbers - or different scales -  in a foreign 
key
shouldn't matter to the optimizer.  The exception - which may have been changed 
- 
was the representation of int64. Having a single key format for numbers makes 
it simple to increase the size of columns or change the scale.

Good luck,

Ann



---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Foreign key different field type

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

On Mon, Jun 19, 2017 at 1:21 PM, Thomas Steinmaurer t...@iblogmanager.com
[firebird-support]  wrote:

> ,
> >
> > Can someone show me example when it is usefull to have different field
> > type in [referenced and referencing keys of a foreign key relationship]?
> >
> >
> > Why this is not forbidden?
>

Why should it be forbidden?  Not everything that's dumb is disallowed.

>
> Perhaps cause the referenced column in table test1 can only store a
> subset (SMALLINT) of the value range of the INTEGER used in test2?
>
> Even if this works at DDL time, I wonder how strict the optimizer then
> is in JOIN statements using an index. Haven't tried.
>

Nor have I tried, but Firebird uses the same key representation for most
numeric
columns, so having mixed sizes of numbers - or different scales -  in a
foreign key
shouldn't matter to the optimizer.  The exception - which may have been
changed -
was the representation of int64. Having a single key format for numbers
makes
it simple to increase the size of columns or change the scale.

Good luck,

Ann

Re: [firebird-support] Foreign key different field type

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

Well, I would hope that

create procedure ...
declare variable i2 TYPE OF COLUMN test2.id1;
declare variable i TYPE OF COLUMN test.id;
begin
  i2 = 12345678;
  i = 12345678;
...

would complain about the assignment to i and not i2.

Although I agree with you that it sounds rather useless to allow foreign
keys to be of a type that is a superset, it also sounds rather harmless (if
properly implemented). Does the SQL standard say anything about this?

Set

Re: [firebird-support] Foreign key different field type

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>At DDL time: What happens if you flip the used data types for ID/ID1?

No difference – constraint created

regards,
Karol Bieniaszewski

From: Thomas Steinmaurer t...@iblogmanager.com [firebird-support] 
Sent: Monday, June 19, 2017 7:21 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Foreign key different field type

  
> Hi,
> 
> Can someone show me example when it is usefull to have different field 
> type in relation
> 
> Sample
> Create table test1 (id smallint not null primary key)
> 
> Create table test2 (id1 integer)
> 
> Alter table test2 Add foreign key (id1) references test1(id)
> 
> Why this is not forbidden?

Perhaps cause the referenced column in table test1 can only store a 
subset (SMALLINT) of the value range of the INTEGER used in test2?

Even if this works at DDL time, I wonder how strict the optimizer then 
is in JOIN statements using an index. Haven't tried.

At DDL time: What happens if you flip the used data types for ID/ID1?

-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.




---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus