How to put foreign key constraints on a computed fields in sql server?
- by Asaf R
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.
