How to put foreign key constraints on a computed f

Question

Table A has a computed field called Computed1. It's persisted and not null. Also, it always computes to an expression which is char(50). It's also unique and has a unique key constraint on it.

Table B has a field RefersToComputed1, which should refer to a valid Computed1 value.

Trying to create a foreign key constraint on B's RefersToComputed1 that references A' Computed1 leads to the following error:

Error SQL01268: .Net SqlClient Data Provider: Msg 1753, Level 16, State 0, Line 1 Column
'B.RefersToComputed1' is not the same length or scale as referencing column 'A.Computed1' in
foreign key 'FK_B_A'. Columns participating in a foreign key relationship must be defined with
the same length and scale.

Q: Why is this error created? Are there special measures needed for foreign keys for computed columns, and if so what are they?

---

Summary:

• The specific problem rises from computed, char based, fields being varchar. Hence, Computed1 is varchar(50) and not char(50).
• It's best to have a cast surrounding a computed field's expression to force it to a specific type. Credit goes to Cade Roux for this tip.

Answer 1

Is RefersToComputed1 exactly the same datatype, length and collation exactly as Computed1?

Double check it... for example, do you need a final CAST or COLLATE on Computed1 to make sure it is what you expect? I say this because the error is saying that the 2 columns are different

Edit: char and varchar are not identical datatypes, so you'll need a CAST to change it

Answer 2

Is RefersToComputed1 a primary key of type char(50)?

Answer 3

The computed field is composed of char(M), char(N) and so on, that add up to M+N+.. = 50, but the computed field itself is varchar(50). Changing RefersToComputed1 to varchar(50) instead of char(50) solves the problem.

Computed fields foreign keys require no special treatment (although persisted might be required on the computed column).