Commented by: @dyemanov

Out of curiosity, what practical problems do you see here? The SQL specification says that the declared type of each referencing column shall be comparable to the declared type of the corresponding referenced column. INT and SMALLINT are perfectly comparable, I'd say.

Commented by: @livius2

Only clarity of data and waste of db space

you know that you never can insert in colum ID_AAA data bigger then smallint
and you know that sometimes sql standard say something but should not
you remember CORE2812 "Prohibit any improper mixture of explicit and implicit joins"...

and extream sample (of course i never see varchar size reference like this but..) wasting 900 bytes

CREATE TABLE AAAA
(
ID VARCHAR(10) NOT NULL PRIMARY KEY
);

CREATE TABLE BBBB
(ID SMALLINT NOT NULL PRIMARY KEY,
ID_AAAA VARCHAR(1000)
);

commit;

alter table bbbb
add FOREIGN KEY(ID_AAAA) REFERENCES AAAA(ID) ON UPDATE CASCADE ON DELETE NO ACTION;

Commented by: @livius2

and try this in exchanged field types ID from referenced table is bigger field size than can be stored
do you really think that this should be possible

CREATE TABLE AAA
(
ID INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE BBB
(ID SMALLINT NOT NULL PRIMARY KEY,
ID_AAA SMALLINT
);

commit;

alter table bbb
add FOREIGN KEY(ID_AAA) REFERENCES AAA(ID) ON UPDATE CASCADE ON DELETE NO ACTION;

Commented by: @dyemanov

Index keys for ints and smallints are of the same size, both index keys and records are compressed. Thus I don't see problems with wasted db space.
Your last example is a good one though. That said, I'm still not sure it should be treated like a bug. Do you know how other databases handle this situation?

Commented by: @aafemt

I would suggest different example:

create table master (id varchar(3) primary key);
insert into master values ('1');
insert into master values ('01');
create table slave (master_id integer references master on delete cascade);

Varchar and integer are comparable.
Nevertheless, I would insist, that this is a bad database design, but not DBMS problem. So - not a bug.

Commented by: @mrotteveel

Why should that be impossible? It may be nonsensical in this case (as you are actually restricting ID_AAA to values in a SMALLINT), but it will work and not result in problems or errors.

Commented by: @livius2

>>Dmitry Yemanov
>>Do you know how other databases handle this situation?

MSSQL run into error
"Column 'http://AAA.ID' is not the same datatype as referencing column 'BBB.ID_AAA' ..."

Commented by: Sean Leyne (seanleyne)

Karol, the fact MS SQL considers it to be an error does not mean that Firebird can not use an approach which is more forgiving.

Commented by: @livius2

I know that MSSQL is not determinant
but provide me one good sample when this relaxed control of joined field types is usefull
and not generates errors after e.g. year of system work when e.g smallint become too small to store integer relation like in my third example cloned below?

CREATE TABLE AAA
(
ID INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE BBB
(ID INTEGER NOT NULL PRIMARY KEY,
ID_AAA SMALLINT
);

commit;

alter table bbb
add FOREIGN KEY(ID_AAA) REFERENCES AAA(ID) ON UPDATE CASCADE ON DELETE NO ACTION;

Commented by: @asfernandes

You can assign/compare variables of different types, so not everything is extremely strict.

Commented by: @livius2

i can undarstand that someone can need this from some reason, i see in my life many strange but working solution and this is ok. my question is now is possible to have config option for this settings? i beleve that only 1%% of developers will need this option to be turned off

Commented by: @dyemanov

We don't add config options for such reasons.

Commented by: @asfernandes

This is a job for a "schema analyzer" tool. I don't know if a tool like this exists for Firebird.