sql - tener - ver constraint de una tabla oracle

¿Cómo descartar la restricción por defecto de SQL sin saber su nombre?

Question

En Microsoft SQL Server, sé que la consulta para verificar si existe una restricción predeterminada para una columna y descartar una restricción predeterminada es:

IF EXISTS(SELECT * FROM sysconstraints
WHERE id=OBJECT_ID(''SomeTable'')
AND COL_NAME(id,colid)=''ColName''
AND OBJECTPROPERTY(constid, ''IsDefaultCnst'')=1)
ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTable_ColName

Pero debido a un error tipográfico en versiones anteriores de la base de datos, el nombre de la restricción podría ser DF_SomeTable_ColName o DF_SmoeTable_ColName .

¿Cómo puedo eliminar la restricción predeterminada sin ningún error SQL? Los nombres de restricción predeterminados no aparecen en la tabla INFORMATION_SCHEMA, lo que hace que las cosas sean un poco más complicadas.

Por lo tanto, algo como ''eliminar la restricción predeterminada en esta tabla / columna'', o ''eliminar DF_SmoeTable_ColName '', pero no da ningún error si no puede encontrarlo.

Answer 1

Útil para algunas columnas que tenían múltiples default constraints or check constraints creadas:

Escritura modificada de https://stackoverflow.com/a/16359095/206730

Nota: este script es para sys.check_constraints

declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @constraint_name nvarchar(128)
declare @constraint_definition nvarchar(512)
declare @df_name nvarchar(128)
declare @cmd nvarchar(128)
PRINT ''DROP CONSTRAINT [Roles2016.UsersCRM].Estado''
declare constraints cursor for
select t.name TableName, c.name ColumnName, d.name ConstraintName, d.definition ConstraintDefinition
from sys.tables t
join sys.check_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = N''Roles2016.UsersCRM'' and c.name = N''Estado''
open constraints
fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
while @@fetch_status = 0
BEGIN
print ''CONSTRAINT: '' + @constraint_name
select @cmd = ''ALTER TABLE ['' + @table_name + ''] DROP CONSTRAINT ['' + @constraint_name + '']''
print @cmd
EXEC sp_executeSQL @cmd;
fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
END
close constraints
deallocate constraints

Answer 2

Ampliando el código de Mitch Wheat, la siguiente secuencia de comandos generará el comando para soltar la restricción y ejecutarla dinámicamente.

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command nvarchar(1000)
set @schema_name = N''MySchema''
set @table_name = N''Department''
set @col_name = N''ModifiedDate''
select @Command = ''ALTER TABLE '' + @schema_name + ''.'' + @table_name + '' drop constraint '' + d.name
from sys.tables t
join sys.default_constraints d
on d.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = @table_name
and t.schema_id = schema_id(@schema_name)
and c.name = @col_name
--print @Command
execute (@Command)

Answer 3

Descubrí que esto funciona y no usa combinaciones:

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID(''[tableSchema].[tableName]'') AND [name] = ''columnName'';
EXEC(''ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT '' + @ObjectName)

Solo asegúrese de que columnName no tenga corchetes porque la búsqueda busca una coincidencia exacta y no devolverá nada si es [columnName].

Answer 4

Ejecute este comando para explorar todas las restricciones:

exec sp_helpconstraint ''mytable'' --and look under constraint_name.

Se verá más o menos así: DF__Mytable__Column__[ABC123] . Entonces solo puedes soltar la restricción.

Answer 5

Elimine todas las contstraints predeterminadas en una base de datos: seguro para el umbral nvarchar (máximo).

/* WARNING: THE SAMPLE BELOW; DROPS ALL THE DEFAULT CONSTRAINTS IN A DATABASE */
/* MAY 03, 2013 - BY WISEROOT */
declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @df_name nvarchar(128)
declare @cmd nvarchar(128)
declare table_names cursor for
SELECT t.name TableName, c.name ColumnName
FROM sys.columns c INNER JOIN
sys.tables t ON c.object_id = t.object_id INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
ORDER BY T.name, c.name
open table_names
fetch next from table_names into @table_name , @column_name
while @@fetch_status = 0
BEGIN
if exists (SELECT top(1) d.name from sys.tables t join sys.default_constraints d on d.parent_object_id = t.object_id join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id where t.name = @table_name and c.name = @column_name)
BEGIN
SET @df_name = (SELECT top(1) d.name from sys.tables t join sys.default_constraints d on d.parent_object_id = t.object_id join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id where t.name = @table_name and c.name = @column_name)
select @cmd = ''ALTER TABLE ['' + @table_name + ''] DROP CONSTRAINT ['' + @df_name + '']''
print @cmd
EXEC sp_executeSQL @cmd;
END
fetch next from table_names into @table_name , @column_name
END
close table_names
deallocate table_names

Answer 6

Espero que esto pueda ser útil para quién tiene un problema similar. En la ventana de ObjectExplorer , seleccione su base de datos => Tablas, => su tabla => Restricciones. Si el cliente está definido en create column time, puede ver el nombre predeterminado de la restricción, incluido el nombre de la columna. luego usa:

ALTER TABLE yourTableName DROP CONSTRAINT DF__YourTa__NewCo__47127295;

(el nombre de la restricción es solo un ejemplo)

Answer 7

Genere siempre script y revisión antes de ejecutar. Debajo del script

 select ''Alter table dbo.'' + t.name + '' drop constraint ''+ d.name from sys.tables t
join sys.default_constraints d
on d.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where c.name in ( ''VersionEffectiveDate'', ''VersionEndDate'', ''VersionReasonDesc'')
order by t.name

Answer 8

La publicación de blog de Rob Farley podría ser de ayuda:

• Dos formas de encontrar / eliminar una restricción predeterminada sin saber su nombre

Algo como:

 declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N''Department''
set @col_name = N''ModifiedDate''
select t.name, c.name, d.name, d.definition
from
sys.tables t
join sys.default_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where
t.name = @table_name
and c.name = @col_name

Answer 9

La siguiente solución eliminará la restricción predeterminada específica de una columna de la tabla

Declare @Const NVARCHAR(256)
SET @Const = (
SELECT TOP 1 ''ALTER TABLE'' + YOUR TABLE NAME +'' DROP CONSTRAINT ''+name
FROM Sys.default_constraints A
JOIN sysconstraints B on A.parent_object_id = B.id
WHERE id = OBJECT_ID(''YOUR TABLE NAME'')
AND COL_NAME(id, colid)=''COLUMN NAME''
AND OBJECTPROPERTY(constid,''IsDefaultCnst'')=1
)
EXEC (@Const)

Answer 10

Para eliminar la restricción de varias columnas:

declare @table_name nvarchar(256)
declare @Command nvarchar(max) = ''''
set @table_name = N''ATableName''
select @Command = @Command + ''ALTER TABLE '' + @table_name + '' drop constraint '' + d.name + CHAR(10)+ CHAR(13)
from sys.tables t
join sys.default_constraints d
on d.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = @table_name
and c.name in (''column1'',
''column2'',
''column3''
)
--print @Command
execute (@Command)

Answer 11

Solución expandida (toma en cuenta el esquema de la tabla):

-- Drop default contstraint for SchemaName.TableName.ColumnName
DECLARE @schema_name NVARCHAR(256)
DECLARE @table_name NVARCHAR(256)
DECLARE @col_name NVARCHAR(256)
DECLARE @Command NVARCHAR(1000)
set @schema_name = N''SchemaName''
set @table_name = N''TableName''
set @col_name = N''ColumnName''
SELECT @Command = ''ALTER TABLE ['' + @schema_name + ''].['' + @table_name + ''] DROP CONSTRAINT '' + d.name
FROM sys.tables t
JOIN sys.default_constraints d
ON d.parent_object_id = t.object_id
JOIN sys.schemas s
ON s.schema_id = t.schema_id
JOIN sys.columns c
ON c.object_id = t.object_id
AND c.column_id = d.parent_column_id
WHERE t.name = @table_name
AND s.name = @schema_name
AND c.name = @col_name
EXECUTE (@Command)

Answer 12

Tenía algunas columnas que tenían múltiples restricciones predeterminadas creadas, así que creo el siguiente procedimiento almacenado:

CREATE PROCEDURE [dbo].[RemoveDefaultConstraints] @table_name nvarchar(256), @column_name nvarchar(256)
AS
BEGIN
DECLARE @ObjectName NVARCHAR(100)
START: --Start of loop
SELECT
@ObjectName = OBJECT_NAME([default_object_id])
FROM
SYS.COLUMNS
WHERE
[object_id] = OBJECT_ID(@table_name)
AND [name] = @column_name;
-- Don''t drop the constraint unless it exists
IF @ObjectName IS NOT NULL
BEGIN
EXEC (''ALTER TABLE ''+@table_name+'' DROP CONSTRAINT '' + @ObjectName)
GOTO START; --Used to loop in case of multiple default constraints
END
END
GO
-- How to run the stored proc. This removes the default constraint(s) for the enabled column on the User table.
EXEC [dbo].[RemoveDefaultConstraints] N''[dbo].[User]'', N''enabled''
GO
-- If you hate the proc, just get rid of it
DROP PROCEDURE [dbo].[RemoveDefaultConstraints]
GO

Answer 13

 declare @ery nvarchar(max)
declare @tab nvarchar(max) = ''myTable''
declare @qu nvarchar(max) = ''alter table ''+@tab+'' drop constraint ''
select @ery = (select bj.name from sys.tables as tb
inner join sys.objects as bj
on tb.object_id = bj.parent_object_id
where tb.name = @tab and bj.type = ''PK'')
exec(@qu+@ery)
**Take a look**