programing

SQL Server 2005 제약 조건이 있는 드롭 열

lastcode 2023. 6. 26. 21:24
반응형

SQL Server 2005 제약 조건이 있는 드롭 열

"DEFAULT" 제약 조건이 있는 열이 있습니다.해당 열을 삭제하는 스크립트를 만들고 싶습니다.

문제는 이 오류가 반환된다는 것입니다.

Msg 5074, Level 16, State 1, Line 1  
The object 'DF__PeriodSce__IsClo__4BCC3ABA' is dependent on column 'IsClosed'. 
Msg 4922, Level 16, State 9, Line 1 
ALTER TABLE DROP COLUMN IsClosed failed because one or more objects access this column.

열 및 관련 제약 조건을 삭제하는 쉬운 방법을 찾을 수 없었습니다(시스템 테이블을 들여다보는 큰 스크립트만 발견했습니다...).반드시 (!!) "좋은" 방법이 있어야 합니다.)

그리고 DEFAULT 제약 조건의 이름이 임의로 생성되었기 때문에 이름으로 삭제할 수 없습니다.

업데이트:
제약 조건 유형은 "DEFAULT"입니다.

여러분들이 제안한 해결책을 봤는데 정말 "더럽다"고 생각합니다.그렇게 생각하지 않니?Oracle과 MySQL 중 어느 쪽인지는 모르겠지만 다음과 같은 작업이 가능합니다.

DROP COLUMN xxx CASCADE CONSTRAINTS

그리고 관련된 모든 제약을 제거합니다.또는 적어도 해당 열에 매핑된 제약 조건(최소한 CHECK 제약 조건!)을 자동으로 삭제합니다.

MSSQL에는 그런 것이 없습니까?

다음은 열을 기본 제약 조건과 함께 삭제하는 스크립트입니다.를 바꿉니다.MYTABLENAME그리고.MYCOLUMNNAME적당하게.

declare @constraint_name sysname, @sql nvarchar(max)

select @constraint_name = name 
from sys.default_constraints 
where parent_object_id = object_id('MYTABLENAME')
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id('MYTABLENAME')
    and name = 'MYCOLUMNNAME'
    )

set @sql = N'alter table MYTABLENAME drop constraint ' + @constraint_name
exec sp_executesql @sql

alter table MYTABLENAME drop column MYCOLUMNNAME

go

이 쿼리는 지정된 테이블에 대한 기본 제약 조건을 찾습니다.꽤 예쁘군요, 저도 동의합니다.

select 
    col.name, 
    col.column_id, 
    col.default_object_id, 
    OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, 
    dobj.name as def_name
from sys.columns col 
    left outer join sys.objects dobj 
        on dobj.object_id = col.default_object_id and dobj.type = 'D' 
where col.object_id = object_id(N'dbo.test') 
and dobj.name is not null

[EDIT] Julien N의 코멘트에 따라 업데이트됨

아마도 조금 더 도움이 될 것입니다.

declare @tablename nvarchar(200)
declare @colname nvarchar(200)
declare @default sysname, @sql nvarchar(max)

set @tablename = 'your table'
set @colname = 'column to drop'

select @default = name 
from sys.default_constraints 
where parent_object_id = object_id(@tablename)
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id(@tablename)
    and name = @colname 
    )

set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
exec sp_executesql @sql

set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
exec sp_executesql @sql

열을 삭제하려면 @tablename 및 @colname 변수만 설정하면 됩니다.

> select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

여기에 설명된 것처럼 올바른 솔루션이 아닙니다. http://msdn.microsoft.com/en-us/library/aa175912.aspx 에서 다음을 수행합니다.

열 기본 제약 조건의 이름이 ANSI COLUMNS 보기에 유지되지 않으므로 시스템 테이블로 돌아가 이름을 찾아야 합니다.

DEFAULT 제약 조건의 이름을 얻을 수 있는 유일한 방법은 다음 요청입니다.

select  
    t_obj.name              as TABLE_NAME
    ,c_obj.name             as CONSTRAINT_NAME
    ,col.name               as COLUMN_NAME

from    sysobjects  c_obj
join    sysobjects  t_obj on c_obj.parent_obj = t_obj.id  
join    sysconstraints con on c_obj.id  = con.constid
join    syscolumns  col on t_obj.id = col.id
            and con.colid = col.colid
where
    c_obj.xtype = 'D'

삭제하려는 열에만 관련된 제약 조건을 쉽게 삭제할 수 없는 것이 미친 것은 나뿐입니까?
이름을 얻기 위해서는 3개의 조인으로 요청을 실행해야 합니다...

저는 또한 계단식 드롭을 사용할 수 없다는 것이 SQL 서버의 단점이라고 생각합니다.저는 여기에 설명된 다른 사람들과 같은 방식으로 시스템 테이블을 쿼리함으로써 이 문제를 해결했습니다.

  • INFORMATION_SCHEMA.CONSTARINT_COLUM_USAGE에는 외부 키, 기본 키 및 고유 제약 조건만 나열됩니다.
  • 기본 제약 조건을 찾는 유일한 방법은 sys.default_constraints에서 해당 제약 조건을 찾는 것입니다.
  • 여기서 아직 언급되지 않은 것은 인덱스를 사용하면 열을 삭제할 수 없으므로 열을 삭제하기 전에 열을 사용하는 모든 인덱스를 삭제해야 한다는 것입니다.

결과 스크립트는 예쁘지 않지만 재사용할 수 있도록 저장 프로시저에 넣었습니다.

CREATE PROCEDURE DropColumnCascading @tablename nvarchar(500), @columnname nvarchar(500)
AS

SELECT CONSTRAINT_NAME, 'C' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, 'C'
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, 'I'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']'
            WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']'
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']'

print @sql
EXEC sp_executesql @sql

pvolders의 답변은 제가 필요로 하는 것이었지만 원인과 오류가 되는 통계를 놓쳤습니다.이 코드는 저장 프로시저를 만들고 통계를 열거한 후 삭제하는 것을 제외하고 동일한 코드입니다.이것이 제가 생각할 수 있는 최선의 방법이므로 삭제해야 할 통계를 결정할 수 있는 더 나은 방법이 있다면 추가해 주십시오.

DECLARE @tablename nvarchar(500), 
        @columnname nvarchar(500)

SELECT  @tablename = 'tblProject',
        @columnname = 'CountyKey'


SELECT CONSTRAINT_NAME, 'C' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, 'C'
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, 'I'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

INSERT INTO #dependencies
SELECT s.NAME, 'S'
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc 
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c 
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID(@tableName)
AND c.NAME = @columnname
AND s.NAME LIKE '_dta_stat%'

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']'
            WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']'
            WHEN 'S' THEN 'DROP STATISTICS [' + @tablename + '].[' + @dep_name + ']'
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']'

print @sql
EXEC sp_executesql @sql

information_schema 시스템 뷰를 쿼리하여 제약 조건 이름을 가져올 수 있습니다.

select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

Jeremy Stein의 답변을 바탕으로 저장 프로시저를 만들고 기본 제약 조건이 있거나 없는 열을 삭제하는 데 사용할 수 있도록 설정했습니다.sys.column을 두 번 쿼리하기 때문에 효율적이지는 않지만 작동합니다.

CREATE PROCEDURE [dbo].[RemoveColumnWithDefaultConstraints] 
    -- Add the parameters for the stored procedure here
    @tableName nvarchar(max), 
    @columnName nvarchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    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)

    IF EXISTS(SELECT * FROM sys.columns WHERE Name = @columnName  
        AND Object_ID = Object_ID(@tableName))
        EXEC('ALTER TABLE ' + @tableName + ' DROP COLUMN ' + @columnName)       
END
GO

제약 조건의 이름을 조회하거나 MSSQL 설계 보기를 사용하는 것이 항상 선택사항은 아닙니다.현재 제약 조건이 있는 열을 삭제하는 스크립트를 만들고 싶습니다.이름이 생성되고 개발/Sys/Prod/ 등 다른 환경에서 스크립트를 사용하고 싶기 때문에 이름을 사용하는 것은 옵션이 아닙니다.그러면 환경마다 제약 조건 이름이 다르기 때문에 이름을 사용할 수 없습니다.저는 아마 시스템 표를 조사해야 할 것입니다만, 더 쉬운 옵션이 있을 것이라는 데 동의합니다.

열을 내리기 전에 제약 조건을 명시적으로 내리는 것이 "더 깨끗한" 해결책이라고 생각합니다.이렇게 하면, 여러분은 여러분이 알지 못할 수 있는 제약 조건을 포기하지 않을 것입니다.삭제가 여전히 실패하면 추가 제약 조건이 남아 있다는 것을 알 수 있습니다.저는 제 데이터베이스에서 일어나는 일을 정확히 통제하는 것을 좋아합니다.

또한 드롭을 스크립팅하면 스크립트가 명시적으로 보장되며 결과가 원하는 방식으로 정확히 반복될 수 있기를 바랍니다.

랜덤 생성이라니요?관리 스튜디오 또는 sys.tables 보기를 통해 특정 열에 대한 제약 조건을 조회하고 이름이 무엇인지 찾을 수 있습니다.

그런 다음 열을 삭제하기 전에 제약 조건을 삭제하도록 스크립트를 변경할 수 있습니다.이것은 어떤 종류의 제약입니까?외부 키 제약 조건인 경우 이 작업을 수행하면 데이터베이스 내의 데이터 무결성이 손상되지 않습니다.

저는 이것을 우연히 만났어요.MSSQL 설계 보기를 사용하여 제약 조건이 있는 열을 삭제할 수 있습니다.제약 조건이 있든 없든 삭제할 열을 마우스 오른쪽 버튼으로 클릭하면 문제 없이 삭제할 수 있습니다.벌써 바보처럼 보였어요

테이블에 대한 스크립트를 생성합니다.모든 제약 조건의 이름을 찾을 수 있습니다.

언급URL : https://stackoverflow.com/questions/314998/sql-server-2005-drop-column-with-constraints

반응형

'programing' 카테고리의 다른 글

구조 패킹은 결정론적입니까?  (0) 2023.06.26
스프링 부트 자동 배선이 구성 클래스에서 작동하지 않음  (0) 2023.06.26
데이터를 파일이 아닌 문자열로 CSV 형식으로 쓰는 방법은 무엇입니까?  (0) 2023.06.26
이 컨텍스트에서는 숫자 또는 부울을 반환하는 변수 식만 허용됩니다.  (0) 2023.06.26
수출 신고형 Xyz'는 '수출 신고형 Xyz'에 비해 무엇을 의미합니까?  (0) 2023.06.26

'programing'의 다른글

  • 현재글SQL Server 2005 제약 조건이 있는 드롭 열

관련글

댓글

티스토리툴바