如何向MS SQL中的现有（填充）表中添加NOT NULL FOREIGN KEY列？ [英] How do you add a NOT NULL FOREIGN KEY column to an existing (populated) table in MS SQL?

问题描述

我需要为现有（填充）表添加一个NOT NULL列，该表将是另一个表的外键。这会带来两个问题：

I need to add a NOT NULL column to an existing (populated) table that will be a foreign key to another table. This brings about two problems:

1. 添加列时，其值不能为null - 使用默认值不是选项（除非以后被删除），因为数据库逻辑在服务器端验证时用于用户输入新记录时，即当应用程序尝试添加具有此值为空的字段的记录时，数据库会抛出一个错误：

1. When you add the column, its value cannot be null - using a default is not an option (unless it is removed later on) because the database logic is used in the server side validation when a user enters a new record i.e. when the application tries to add a record with this field having a null value, the database throws an error that the application catches and returns to the user, prompting them to correct it.

该列具有外键约束，这意味着它的值必须存在于外部表中

The column has a foreign key constraint, meaning its value MUST exist in the foreign table as well.

最好的方法是什么？

推荐答案

创建列，但允许NULL。
使用外键表中的正确数据填充该列。
改变列添加不为null。
添加外键约束。

Create the column, but allow NULL. Populate the column with the correct data from the foreign key table. Alter the column add not null. Add the foreign key constraint.

这篇关于如何向MS SQL中的现有（填充）表中添加NOT NULL FOREIGN KEY列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！