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**