6

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.

Improve this question
2
  • Neither 2008 nor 2014 accepts foreign key between char and varchar (according to sqlfiddle). Whether this was legal in earlier version of sqlserver I don't no, but many other DBMS accepts it, so I would not be surprised if it once where accepted.
    – Lennart - Slava Ukraini
    Commented Jul 13, 2015 at 20:42
  • 1
    I'm making my mind to install nt4 with sql 6.5 just to check...
    – Paolo
    Commented Jul 13, 2015 at 21:50

1 Answer 1

Reset to default
8

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.

Improve this answer
6
  • Interesting! Was it allowed for a char(8) to reference a char(9)? A char(9) to reference a char(8)? A varchar to reference a nvarchar?
    – ypercubeᵀᴹ
    Commented Jul 17, 2015 at 21:36
  • 4
    This was indeed a breaking change from 6.5 to 7. See sqlmag.com/sql-server/migrating-sql-server-65-70. I have no idea how long compatibility was maintained. Probably as long as 6.5 database compatibility. SQL Server 7 installation media is hard to find because it was withdrawn due to Java licencing issues.
    – Paul White ♦
    Commented Jul 17, 2015 at 22:05
  • 1
    @PaulWhite - Yep this test on 2005 shows it was preserved that long i.sstatic.net/2w7cj.png (2005 supported 60/ 65/70, 2008 just 80 up)
    – Martin Smith
    Commented Jul 18, 2015 at 12:28
  • 1
    @ypercube - From testing on 2005 with 6/6.5 compatibility mode this would not be allowed Columns participating in a foreign key relationship must be defined with the same length.
    – Martin Smith
    Commented Jul 18, 2015 at 16:36
  • 3
    @Paolo - You'd be looking for "breaking changes" in the release notes for SQL Server 7 but I don't think much documentation is online for SQL Server prior to 2000.
    – Martin Smith
    Commented Jul 18, 2015 at 17:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.