如何把外键约束在SQL Server中的计算字段？ [英] How to put foreign key constraints on a computed fields in sql server?

问题描述

表A有一个名为Computed1的计算字段。它是持久，而不是空。此外，它总是计算一个表达式是char（50）。它也是独一无二的，并有一个独特的关键约束。

表B有一个字段RefersToComputed1，它应该引用一个有效的Computed1值。

尝试在B的RefersToComputed1上创建一个引用A'Computed1的外键约束导致以下错误：

 错误SQL01268：.Net SqlClient数据提供程序：消息1753，级别16，状态0，行1列
B.RefersToComputed1与引用列的长度或比例不同
外键FK_B_A中的A.Computed1。参与外键关系的列必须使用
定义相同的长度和比例。 
  

问：为什么会产生这个错误？计算列的外键是否需要特殊的措施？如果是这样的话，它们是什么？

---

：

• 具体问题来自计算的基于char的字段varchar。因此，Computed1是varchar（50）而不是char（50）。
  
• 最好是围绕计算字段的表达式强制转换为特定的类型。这个技巧来自Cade Roux。
  
  

  解决方案

  计算字段由char M），char（N）等等，加起来为M + N + .. = 50，但计算的字段本身是varchar（50）。将RefersToComputed1更改为varchar（50）而不是char（50）解决了这个问题。
  
  

  计算字段外键不需要特殊处理（尽管可能需要计算列）。

  
  

  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.

  解决方案

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

  这篇关于如何把外键约束在SQL Server中的计算字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！