SQL 기본 제약 조건을 이름을 모르면서 삭제하는 방법

programing

SQL 기본 제약 조건을 이름을 모르면서 삭제하는 방법

topblog 2023. 4. 8. 07:51

SQL 기본 제약 조건을 이름을 모르면서 삭제하는 방법

Microsoft SQL Server에서 열에 대한 기본 제약 조건이 있는지 확인하고 기본 제약 조건을 삭제하는 쿼리는 다음과 같습니다.

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

그러나 이전 버전의 데이터베이스 오타로 인해 제약조건의 이름은 다음과 같습니다.DF_SomeTable_ColName또는DF_SmoeTable_ColName.

SQL 오류 없이 기본 제약 조건을 삭제하려면 어떻게 해야 합니까?기본 제약 조건 이름은 INFORMATION_SCHEMA 테이블에 표시되지 않기 때문에 좀 더 까다로워집니다.

즉, '이 테이블/컬럼의 기본 구속조건을 삭제한다' 또는 '삭제한다'와 같은 것입니다.DF_SmoeTable_ColName'하지만 찾을 수 없는 경우 오류는 주지 마십시오.

Mitch Wheat의 코드를 확장하면 다음 스크립트는 제약을 해제하고 동적으로 실행하는 명령을 생성합니다.

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)

Rob Farley의 블로그 투고는 도움이 될 수 있습니다.

이름을 모르는 상태에서 기본 제약 조건을 찾거나 삭제하는 두 가지 방법

예를 들어 다음과 같습니다.

 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

이것은 기능하며 조인(join)을 사용하지 않습니다.

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)

쿼리는 정확하게 일치하는 항목을 찾고 있으며 [columnName]일 경우 아무것도 반환되지 않으므로 columnName 주위에 괄호가 없는지 확인하십시오.

여러 열에 대한 제약 조건을 삭제하려면:

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)

확장 솔루션(테이블 스키마를 고려):

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

모든 제약 조건을 참조하려면 다음 명령을 수행합니다.

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

다음과 같이 표시됩니다.DF__Mytable__Column__[ABC123]그럼 구속을 풀면 되겠네요.

데이터베이스에서 모든 기본 컨텐트를 삭제합니다. nvarchar(max) 임계값에 대해 안전합니다.

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

나는 이것이 비슷한 문제를 가진 사람에게 도움이 될 수 있기를 바란다.인ObjectExplorer창에서 데이터베이스 = > 테이블 = > 테이블 = > 구속조건을 선택합니다.고객이 열 생성 시간에 정의되어 있는 경우 열 이름을 포함한 제약 조건의 기본 이름을 볼 수 있습니다.다음으로 다음을 사용합니다.

ALTER TABLE  yourTableName DROP CONSTRAINT DF__YourTa__NewCo__47127295;

(제약명은 예에 불과합니다)

다음 솔루션은 컬럼의 특정 기본 구속조건을 테이블에서 삭제합니다.

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)

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)

한번 보세요.

기본 제약 조건이 여러 개 생성된 열이 있으므로 다음과 같은 저장 프로시저를 만듭니다.

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

여러 개의 열이 있는 일부 열에 유용합니다. default constraints or check constraints작성필:

https://stackoverflow.com/a/16359095/206730 스크립트 변경

주의: 이 스크립트는 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

실행 전에 항상 스크립트를 생성하고 검토하십시오.스크립트 아래

  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

declare @table_name nvarchar(100)
declare @col_name nvarchar(100)
declare @constraint nvarchar(100)
set @table_name = N'TableName'
set @col_name = N'ColumnName'

IF EXISTS (select       c.*
    from        sys.columns c 
    inner join  sys.tables t on t.object_id = c.object_id
    where       t.name = @table_name
    and         c.name = @col_name) 
BEGIN

select @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 c.name = @col_name

    IF LEN(ISNULL(@constraint, '')) <> 0
    BEGIN
        DECLARE @sqlcmd VARCHAR(MAX)
        SET @sqlcmd = 'ALTER TABLE ' + QUOTENAME(@table_name) + ' DROP CONSTRAINT' + 
        QUOTENAME(@constraint);
        EXEC (@sqlcmd);

    END

END
GO

그러면 외부 키가 존재하는지 여부가 확인됩니다.그게 존재한다면, 그걸 버리세요.

DECLARE @SCHEMA_NAME NVARCHAR(256)
-- The table name you what drop the foreign key from.
DECLARE @ALTER_TABLE_NAME NVARCHAR(256)
-- The table name is liked with the foreign key.
DECLARE @REF_TABLE_NAME NVARCHAR(256) 
DECLARE @COMMAND  NVARCHAR(MAX)

SET @SCHEMA_NAME = N'MySchema';
SET @ALTER_TABLE_NAME = N'MyAlterTable';
SET @REF_TABLE_NAME = N'MyReferTable';

IF EXISTS (
    SELECT NAME
    FROM SYS.FOREIGN_KEYS
    WHERE PARENT_OBJECT_ID = (
        SELECT OBJECT_ID
        FROM SYS.OBJECTS
        WHERE OBJECT_ID = OBJECT_ID(@ALTER_TABLE_NAME)
    )
    AND REFERENCED_OBJECT_ID = (
        SELECT OBJECT_ID
        FROM SYS.OBJECTS
        WHERE OBJECT_ID = OBJECT_ID(@REF_TABLE_NAME)
    )
)
BEGIN
    SELECT @COMMAND = 'ALTER TABLE ['
        + @SCHEMA_NAME
        + '].['
        + @ALTER_TABLE_NAME
        + '] DROP CONSTRAINT '
        + NAME
    FROM SYS.FOREIGN_KEYS
    WHERE PARENT_OBJECT_ID = (
        SELECT OBJECT_ID
        FROM SYS.OBJECTS
        WHERE OBJECT_ID = OBJECT_ID(@ALTER_TABLE_NAME)
    )
    AND REFERENCED_OBJECT_ID = (
        SELECT OBJECT_ID
        FROM SYS.OBJECTS
        WHERE OBJECT_ID = OBJECT_ID(@REF_TABLE_NAME)
    )
    
    EXECUTE (@COMMAND)
END
GO

간단한 해결 방법이 있습니다._table과 column_name을 교체해 주세요.

DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'${default_schema}.your_table')
  AND col_name(parent_object_id, parent_column_id) = 'column_name';
IF @var0 IS NOT NULL
    EXECUTE ('ALTER TABLE ${default_schema}.your_table DROP CONSTRAINT [' + @var0 + ']');
GO

언급URL : https://stackoverflow.com/questions/1430456/how-to-drop-sql-default-constraint-without-knowing-its-name

'programing' 카테고리의 다른 글

SQL 서버의 서로 다른 두 서버에서 데이터 선택 (0)	2023.04.08
문의 "INSERT IN..." 부분을 반복하지 않고 여러 행을 삽입하시겠습니까? (0)	2023.04.08
SQL Server SELECT IN @variable? (0)	2023.04.08
react.default.createContext는 react-redux를 사용할 때 함수가 아닙니다. (0)	2023.04.03
Jest가 모든 비동기 코드의 실행이 완료될 때까지 기다렸다가 어설션을 기대하는 방법 (0)	2023.04.03

현재글SQL 기본 제약 조건을 이름을 모르면서 삭제하는 방법

각종 프로그래밍 정보를 다루는 블로그입니다.

Excel, sql-server, Oracle, Android, reactjs, Git, Spring-boot, asp.net, python, Ajax, mysql, MariaDB, C, MongoDB, json, wordpress, angularJS, jQuery, CSS, PowerShell,

Today :
Yesterday :

topblog

SQL 기본 제약 조건을 이름을 모르면서 삭제하는 방법

SQL 기본 제약 조건을 이름을 모르면서 삭제하는 방법

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2024/12 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

SQL 기본 제약 조건을 이름을 모르면서 삭제하는 방법

SQL 기본 제약 조건을 이름을 모르면서 삭제하는 방법

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바