restricciones - ¿Cómo elimino una restricción de clave externa solo si existe en el servidor SQL?

sql eliminar clave foranea

Question

Puedo eliminar una tabla si existe usando el siguiente código pero no sé cómo hacer lo mismo con una restricción:

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''TableName'') AND type = (N''U'')) DROP TABLE TableName
go

También agrego la restricción usando este código:

ALTER TABLE [dbo].[TableName]
WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
REFERENCES [dbo].[TableName2] ([ID])
go

Answer 1

Creo que esto te será útil ...

 DECLARE @ConstraintName nvarchar(200)
SELECT
@ConstraintName = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE
KCU.TABLE_NAME = ''TABLE_NAME'' AND
KCU.COLUMN_NAME = ''TABLE_COLUMN_NAME''
IF @ConstraintName IS NOT NULL EXEC(''alter table TABLE_NAME drop CONSTRAINT '' + @ConstraintName)

Se eliminará la restricción de clave externa en función de una tabla y columna específicas.

Answer 2

En SQL Server 2016 puedes usar DROP IF EXISTS:

CREATE TABLE t(id int primary key,
parentid int
constraint tpartnt foreign key references t(id))
GO
ALTER TABLE t
DROP CONSTRAINT IF EXISTS tpartnt
GO
DROP TABLE IF EXISTS t

Ver http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx

Answer 3

Esto es mucho más simple que la solución propuesta actual:

IF (OBJECT_ID(''dbo.FK_ConstraintName'', ''F'') IS NOT NULL)
BEGIN
ALTER TABLE dbo.TableName DROP CONSTRAINT FK_ConstraintName
END

Si necesita eliminar otro tipo de restricción, estos son los códigos aplicables para pasar a la función OBJECT_ID () en la segunda posición del parámetro:

C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
UQ = UNIQUE constraint

También puede utilizar OBJECT_ID sin el segundo parámetro.

Lista completa de tipos here :

Tipo de objeto:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SO = Sequence object

Se aplica a: SQL Server 2012 a través de SQL Server 2014.

SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure

Answer 4

La respuesta aceptada a esta pregunta no parece funcionar para mí. Logré lo mismo con un método ligeramente diferente:

IF (select object_id from sys.foreign_keys where [name] = ''FK_TableName_TableName2'') IS NOT NULL
BEGIN
ALTER TABLE dbo.TableName DROP CONSTRAINT FK_TableName_TableName2
END

Answer 5

La respuesta de James funciona bien si conoces el nombre de la restricción real. Lo difícil es que, en situaciones heredadas y en otros escenarios del mundo real, es posible que no sepa cómo se llama la restricción.

Si este es el caso, corre el riesgo de crear restricciones duplicadas, para evitar que pueda usar:

create function fnGetForeignKeyName
(
@ParentTableName nvarchar(255),
@ParentColumnName nvarchar(255),
@ReferencedTableName nvarchar(255),
@ReferencedColumnName nvarchar(255)
)
returns nvarchar(255)
as
begin
declare @name nvarchar(255)
select @name = fk.name from sys.foreign_key_columns fc
join sys.columns pc on pc.column_id = parent_column_id and parent_object_id = pc.object_id
join sys.columns rc on rc.column_id = referenced_column_id and referenced_object_id = rc.object_id
join sys.objects po on po.object_id = pc.object_id
join sys.objects ro on ro.object_id = rc.object_id
join sys.foreign_keys fk on fk.object_id = fc.constraint_object_id
where
po.object_id = object_id(@ParentTableName) and
ro.object_id = object_id(@ReferencedTableName) and
pc.name = @ParentColumnName and
rc.name = @ReferencedColumnName
return @name
end
go
declare @name nvarchar(255)
declare @sql nvarchar(4000)
-- hunt for the constraint name on ''Badges.BadgeReasonTypeId'' table refs the ''BadgeReasonTypes.Id''
select @name = dbo.fnGetForeignKeyName(''dbo.Badges'', ''BadgeReasonTypeId'', ''dbo.BadgeReasonTypes'', ''Id'')
-- if we find it, the name will not be null
if @name is not null
begin
set @sql = ''alter table Badges drop constraint '' + replace(@name,'']'', '']]'')
exec (@sql)
end

Answer 6

La solución más simple se proporciona en la respuesta de Eric Isaacs. Sin embargo, encontrará restricciones en cualquier tabla. Si desea apuntar a una restricción de clave externa en una tabla específica, use esto:

IF EXISTS (SELECT *
FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N''dbo.FK_TableName_TableName2'')
AND parent_object_id = OBJECT_ID(N''dbo.TableName'')
)
ALTER TABLE [dbo.TableName] DROP CONSTRAINT [FK_TableName_TableName2]

Answer 7

Puede usar esas consultas para encontrar todos los FK para su tabla.

Declare @SchemaName VarChar(200) = ''Schema Name''
Declare @TableName VarChar(200) = ''Table name''
-- Find FK in This table.
SELECT
''IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'''''' +
''['' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + ''].['' + FK.name + '']''
+ '''''') AND parent_object_id = OBJECT_ID(N'''''' +
''['' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + ''].[''
+ OBJECT_NAME(FK.parent_object_id) + '']'' + '''''')) '' +
''ALTER TABLE '' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
''.['' + OBJECT_NAME(FK.parent_object_id) +
''] DROP CONSTRAINT '' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
-- Find the FKs in the tables in which this table is used
SELECT
'' IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'''''' +
''['' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + ''].['' + FK.name + '']''
+ '''''') AND parent_object_id = OBJECT_ID(N'''''' +
''['' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + ''].[''
+ OBJECT_NAME(FK.parent_object_id) + '']'' + '''''')) '' +
'' ALTER TABLE '' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
''.['' + OBJECT_NAME(FK.parent_object_id) +
''] DROP CONSTRAINT '' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName

Answer 8

ALTER TABLE [dbo].[TableName]
DROP CONSTRAINT FK_TableName_TableName2

Answer 9

Declare @FKeyRemoveQuery NVarchar(max)
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N''dbo.TableName''))
BEGIN
SELECT @FKeyRemoveQuery=''ALTER TABLE dbo.TableName DROP CONSTRAINT ['' + LTRIM(RTRIM([name])) + '']''
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N''dbo.TableName'')
EXECUTE Sp_executesql @FKeyRemoveQuery
END

Answer 10

IF (OBJECT_ID(''DF_Constraint'') IS NOT NULL)
BEGIN
ALTER TABLE [dbo].[tableName]
DROP CONSTRAINT DF_Constraint
END