如何在关系数据库表中添加新字段 [英] How can I add new field in my relational database table

问题描述

嗨..

我有一个关系数据库，我有需求表。现在我想在我的表中添加一个字段，但如果我取消选中'Allow Null'复选框，那么表格保存，如果我选中'Allow Null'，它将显示一个包含如下错误的对话框..



我的尝试：

Hi..
I have a relational database in which I have demand table. Now I want to add one more field into my table but if I unchecked the 'Allow Null' checkbox then the table save and if I checked the 'Allow Null' it will show me a dialog box containing the error like below..

What I have tried:

'Employees' table saved successfully
'Items' table saved successfully
'Demands' table
- Unable to modify table.  
Cannot insert the value NULL into column 'Purchased_Date', table 'Inventory_Aamir.dbo.Tmp_Demands'; column does not allow nulls. INSERT fails.
The statement has been terminated.

TITLE: Microsoft SQL Server Management Studio
------------------------------

User canceled out of save dialog
 (MS Visual Database Tools)

------------------------------
BUTTONS:

OK
------------------------------

推荐答案

将列添加到具有现有数据的表时，生成的表必须一致：SQL的作用是有效地创建一个新表，插入所有旧记录，然后删除旧表并重命名新表。如果新列不能采用空值，则新表的插入失败 - 因为SQL没有其他数据要插入该列 - 因此添加列操作本身无法保持数据库的一致性。



您需要在新列上允许空值，或者将列的DEFAULT值设置为合理的值，以便SQL可以将其用于现有行。

When you add a column to a table which has existing data, the resulting table must be consistent: what SQL does is effectively create a new table, insert all the old records, then delete the old table and rename the new. If your new columns cannot take a null value, then the insert to the new table fails - because SQL has no other data to insert in that column - so the "add column" operation itself fails to maintain the DB consistency.

You either need to allow null values on the new column, or set the DEFAULT value for the column to a reasonable value so SQL can use that for existing rows.

这篇关于如何在关系数据库表中添加新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！