Как вы отбрасываете значение по умолчанию или подобное ограничение в T-SQL?

Question

Я знаю синтаксис:

ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint]

но как убрать ограничение по умолчанию, когда я не знаю его имени? (То есть он был автоматически сгенерирован на CREATE TABLE время.)

Answer 1

Если вы хотите сделать это вручную, вы можете использовать Management Studio, чтобы найти его (в узле Ограничения внутри таблицы).

Чтобы сделать это с помощью SQL:

• Если ограничения являются ограничениями по умолчанию, вы можете использовать sys.default_constraints найти его:

  SELECT OBJECT_NAME(parent_object_id) AS TableName, name AS ConstraintName
  FROM sys.default_constraints ORDER BY TableName, ConstraintName
  

• Если вы ищете и другие ограничения (проверка, уникальный, внешний ключ, по умолчанию, первичный ключ), вы можете использовать sysconstraints:

  SELECT OBJECT_NAME(id) AS TableName, OBJECT_NAME(constid) AS ConstraintName
  FROM sysconstraints ORDER BY TableName, ConstraintName
  

Вы не говорите, какую версию SQL Server вы используете. Вышеуказанное работает как на SQL 2005, так и на SQL 2008.

Answer 2

Вы можете использовать этот код, чтобы сделать это автоматически:

DECLARE @tableName VARCHAR(MAX) = '<MYTABLENAME>'
DECLARE @columnName VARCHAR(MAX) = '<MYCOLUMNAME>'
DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name 
FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
AND PARENT_COLUMN_ID = (
    SELECT column_id FROM sys.columns
    WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName))
IF @ConstraintName IS NOT NULL
    EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName)

Просто замени <MYTABLENAME> а также <MYCOLUMNNAME> по мере необходимости.

Answer 3

Вы можете найти имя ограничения по sp_help [имя таблицы] и затем удалить его по имени.

Или вы можете сделать это через Management studio.

Answer 4

Или вы можете найти его, используя представление каталога sys.check_constraints.

Answer 5

Для одной таблицы и столбца в одной строке используйте следующее

declare @sql nvarchar(max); set @sql = ''; SELECT @sql+='ALTER TABLE [dbo].[YOURTABLENAME] DROP CONSTRAINT ' + ((SELECT OBJECT_NAME(constid) FROM sysconstraints WHERE OBJECT_NAME(id) = 'YOURTABLENAME'AND colid IN (SELECT ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS Where Table_Name = 'YOURTABLENAME' and COLUMN_NAME = 'YOURCOLUMNNAM'))) + ';'; EXEC sp_executesql @sql;

Если у вас есть несколько ограничений на столбец, вам нужно будет различать ограничение, за которым вы следите, но если у вас просто есть ограничение по умолчанию, это поможет.

Проверьте другие столбцы, доступные в information_schema, чтобы позволить вам различать дальше.

Answer 6

Здесь идет моя собственная версия, которая отбрасывает все зависимые ограничения - ограничение по умолчанию (если оно существует) и все затронутые проверочные ограничения (как, кажется, предполагает стандарт SQL и как, похоже, некоторые другие базы данных)

declare @constraints varchar(4000);
declare @sql varchar(4000);
with table_id_column_position as (
   select object_id table_id, column_id column_position
      from sys.columns where object_id is not null and object_id = object_id('TableName') and name = 'ColumnToBeDropped'
)
select @constraints = coalesce(@constraints, 'constraint ') + '[' + name + '], '
from sysobjects 
where (
  -- is CHECK constraint
  type = 'C' 
  -- dependeds on the column
  and id is not null
  and id in (
      select object_id --, object_name(object_id)
      from sys.sql_dependencies, table_id_column_position 
      where object_id is not null
      and referenced_major_id = table_id_column_position.table_id
      and referenced_minor_id = table_id_column_position.column_position
    )
) OR (
  -- is DEFAULT constraint
  type = 'D'
  and id is not null
  and id in (
    select object_id
    from sys.default_constraints, table_id_column_position
     where object_id is not null
     and parent_object_id = table_id_column_position.table_id
     and parent_column_id = table_id_column_position.column_position
  )
);
set @sql = 'alter table TableName drop ' + coalesce(@constraints, '') + ' column ColumnToBeDropped';
exec @sql

(Осторожно: оба TableName а также ColumnToBeDropped появляются дважды в коде выше)

Это работает путем построения одного ALTER TABLE TableName DROP CONSTRAINT c1, ..., COLUMN ColumnToBeDropped и выполнить его.