在java怎么更改sql外键_如何在SQL Server中创建外键？

Necromancing .

实际上，正确地做这件事有点棘手 .

首先需要检查是否存在要设置外键引用的列的主键 .

在此示例中，创建表T_ZO_SYS_Language_Forms上的外键，引用dbo.T_SYS_Language_Forms.LANG_UID

-- First, chech if the table exists...

IF 0 < (

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND TABLE_SCHEMA = 'dbo'

AND TABLE_NAME = 'T_SYS_Language_Forms'

)

BEGIN

-- Check for NULL values in the primary-key column

IF 0 = (SELECT COUNT(*) FROM T_SYS_Language_Forms WHERE LANG_UID IS NULL)

BEGIN

ALTER TABLE T_SYS_Language_Forms ALTER COLUMN LANG_UID uniqueidentifier NOT NULL

-- No, don't drop, FK references might already exist...

-- Drop PK if exists

-- ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT pk_constraint_name

--DECLARE @pkDropCommand nvarchar(1000)

--SET @pkDropCommand = N'ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT ' + QUOTENAME((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

--WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

--AND TABLE_SCHEMA = 'dbo'

--AND TABLE_NAME = 'T_SYS_Language_Forms'

----AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms'

--))

---- PRINT @pkDropCommand

--EXECUTE(@pkDropCommand)

-- Instead do

-- EXEC sp_rename 'dbo.T_SYS_Language_Forms.PK_T_SYS_Language_Forms1234565', 'PK_T_SYS_Language_Forms';

-- Check if they keys are unique (it is very possible they might not be)

IF 1 >= (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC)

BEGIN

-- If no Primary key for this table

IF 0 =

(

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

AND TABLE_SCHEMA = 'dbo'

AND TABLE_NAME = 'T_SYS_Language_Forms'

-- AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms'

)

ALTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC)

;

-- Adding foreign key

IF 0 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms')

ALTER TABLE T_ZO_SYS_Language_Forms WITH NOCHECK ADD CONSTRAINT FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms FOREIGN KEY(ZOLANG_LANG_UID) REFERENCES T_SYS_Language_Forms(LANG_UID);

END -- End uniqueness check

ELSE

PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key...'

END -- End NULL check

ELSE

PRINT 'FSCK, need to figure out how to update NULL value(s)...'

END