数据库-执行alter table脚本时出现问题 [英] Database - Problem in Executing alter table scripts

问题描述

大家好，

我在数据库中执行更新脚本时遇到问题.

我正在使用SQL SERVER2005.

我确实是这样的:


1.我正在向 现有数据表中添加新列

2.使用默认值更新新列值.

3.然后应用Not null和外键约束.

我的代码是这样的..

Hai all,

I have a problem in executing update script in database.

I am using SQL SERVER 2005.

I did like this :


1.I am adding a new column to existing table with data

2.Updating new column value with default value.

3.then applying Not null and foreign key constraints.

My code is like this..

USE DB --Database name 
IF EXISTS (SELECT * FROM FkTable) -- Table name : FkTable
	BEGIN

	USE DB
		ALTER TABLE FkTable ADD col SMALLINT  -- New column Name : col
		GO
BEGIN TRANSACTION
	USE DB
		UPDATE  FkTable SET col  = 1 -- data updation
	USE DB
		ALTER TABLE FkTable ALTER COLUMN col SMALLINT NOT NULL -- adding not null constraint
	USE DB
		ALTER TABLE FkTable ADD CONSTRAINT fk_constraint FOREIGN KEY(col) REFERENCES PkTable(CODE) -- adding foreign key constraint--  Primary key table is : PkTable : 
COMMIT TRANSACTION
	END

ELSE
	BEGIN

	USE DB
	ALTER TABLE FkTable ADD col SMALLINT  NOT NULL
	GO
BEGIN TRANSACTION
	USE DB
	ALTER TABLE FkTable ADD CONSTRAINT fk_constraint FOREIGN KEY(col) REFERENCES PkTable(CODE)
COMMIT TRANSACTION
	END	
GO

推荐答案

在IF ELSE条件之间的脚本中，所有这些"GO"都在做什么?首先将其删除.

What are all those "GO"s doing inside the script in between IF ELSE condition? Remove them first.

这篇关于数据库-执行alter table脚本时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！