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
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.