SQL Server Foreign Key – Handling Different Datatypes (char(8) vs varchar(8))

sql server

This is more a quest for information that a request for help…

I'm upgrading some old databases (running on sql 2005 and compatibility level 70, I suspect they are online since sql 6.5) to get rid of a couple of old servers and deploy SQL Server 2012 or 2014.

I had to deal with some sql collation now unsupported so I dropped some indexes and foreign keys. While trying to restore them I've been stopped by this error:

Msg 1778, Level 16, State 0, Line 2
Column 'dbo.Table1.IDColumn' is
not the same data type as referencing column
'Table2.ColumnFK' in foreign key
'Foreign_key'.

The first field is declared as varchar(8) and the second is a char(8) so the message is correct.

I'm doing all the tests on a copy so I double checked on the production database and the foreign key is there, linking a char field with a varchar field.

When scripting the constraint this is what I got (that's the script that is giving error):

ALTER TABLE [dbo].[Table2]
WITH NOCHECK ADD  CONSTRAINT [Foreign_key]
FOREIGN KEY([ColumnFK]) REFERENCES [dbo].[Table1] ([IDColumn])
GO

ALTER TABLE [dbo].[Table2]
NOCHECK CONSTRAINT [Foreign_key]
GO

I found an answer on SO stating that it is not possible to have a foreign key using 2 columns that have different datatype but a workaround is supplied so the issue is not critical.

The question is: was it ever allowed to create a relationship on fields with different data types without tricks? Was there some sort of 'compatibility' between char and varchar?

The answer should be a obvious 'yes, it is possible' but I can't find any evidence stating that this behavior is (was?) allowed and/or expected: I have the foreign key in place but it should not be possible to create it.

Best Answer

Digging into long forgotten boxes I found both nt4 and SQL-Server 6.5 installation media and I can confirm that a foreign key referencing fields with different data types was allowed.

Here is the statement I successfully run on SQL-Server 6.5:

create table tbl1 (code char(8) primary key (code))
create table tbl2 (code varchar(8) primary key (code))
alter table tbl1 add constraint FK foreign key (code) references tbl2 (code)

no trailing semicolon or you'll get a syntax error.

I can't get a grasp on sql 7 media so I can't tell if it was working on that version too.

Related Question