Spis treści:

Kategoria:SQL Server


Jak usunąć wartość domyślną z kolumny w tabeli

Czasami nadchodzi taki dzień, że trzeba usunąć z bazy więzy integralności nadające danej kolumnie wartość domyślną. Tradycyjnie takie więzy usuwa się na dwa sposoby - pierwszy z nich to skorzystanie z SQL Server Management Studio (lub inna aplikacja pomocnicza). Drugi sposób polega na wykonaniu instrukcji:

ALTER TABLE Tabela DROP CONSTRAINT Nazwa

Wydaje się to wszystko bajecznie proste. Co się jednak dzieje, gdy zamiast korzystać z instrukcji bardziej pełnej:

ALTER TABLE Tabela ADD CONSTRAINT NormalnaNazwa DEFAULT (0) FOR Kolumna

korzysta się z SQL Server Management Studio lub wykonuje się skróconą formę instrukcji zaprezentowaną poniżej?

ALTER TABLE Tabela ADD DEFAULT (0) FOR Kolumna

Otóż dzieje się to, że nazwa takiego więzu integralności przyjmuje dziwną postać. Jeżeli w danej tabeli tylko jedna kolumna przyjmuje wartości domyślne, to nie stwarza problemu odgadnięcie, że DF__Tabela_ST_LR____48274824 to akurat ten obiekt. Jeżeli wiele kolumn przyjmuje wartości domyślne, to wyszukkanie właściwego obiektu do usunięcia może już być kłopotliwe. Drugi przypadek może być bardziej interesujący. Przypuśćmy, że dwie osoby pracują nad jedną bazą i każda z tych osób ma oddzielną kopię. Obaj mają te same tabele i obaj dowiedzieli się, że muszą ustawić wartość domyślną na kolumnie. Obaj skorzystali z SQL Server Management Studio. Teraz najważniejsze pytanie: Czy nazwy więzów u obu osób są takie same? Od razu odpowiadam: Nie! Załóżmy, że pierwsza osoba chce zmienić coś w bazie i modyfikuje wartość domyślną. Tworzy potem skrypt SQL, podsyła drugiej osobie, tamta go wykonuje i co? I znów problem, bo druga osoba nie ma czegoś takiego jak DF__Tabela_ST_ID____39473534.

Przypadki mogą być różne, ale na szczęście istnieje rozwiązanie.

DECLARE @tab NVARCHAR(128) = 'Tabela'
DECLARE @col NVARCHAR(128) = 'Kolumna'

DECLARE @sql NVARCHAR(512)
DECLARE @name NVARCHAR(32)
SELECT @name=CS.[name] 
FROM sys.default_constraints CS
JOIN sys.tablesON T.[object_id]=CS.[parent_object_id]
JOIN sys.columnsON C.[column_id]=CS.[parent_column_id]
WHERE T.[name]=@tab
AND C.[name]=@col

SET @sql='ALTER TABLE '+@tab+' DROP CONSTRAINT '+@name

SELECT @sql [Skrypt]
EXEC sp_sqlexec @sql

Ten prosty skrypt opiera swoje działanie o wyniki zwrócone z widoku systemowego sys.default_constraints. Widoki sys.columns oraz sys.tables łączone są w celu pobrania przyjaznych nazw, zamiast korzystania z identyfikatorów lub funkcji OBJECT_ID. Jak z tego korzystać? Skopiować, podmienić nazwę tabeli i kolumny i wykonać. Przedostatnia linijka nie jest potrzebna, ale pozwala wyświetlić skrypt, który rzeczywiście został wykonany w celu usunięcia wartości domyślnej z kolumny. Całość można oczywiście zapisać bardziej zwięźle, ale w takiej formie lepiej widać kolejne etapy skryptu.

Nic nie stoi na przeszkodzie, aby stworzyć sobie procedurę, umieścić ją w bazie i wykonywać, gdy zajdzie taka potrzeba. Skrypt potrzebny do utworzenia procedury znajduje się poniżej:

CREATE PROCEDURE dbo.DropDefault
@tab NVARCHAR(128),
@col NVARCHAR(128)
AS
DECLARE @sql NVARCHAR(512)
DECLARE @name NVARCHAR(32)
SELECT @name=CS.[name] 
FROM sys.default_constraints CS
JOIN sys.tablesON T.[object_id]=CS.[parent_object_id]
JOIN sys.columnsON C.[column_id]=CS.[parent_column_id]
WHERE T.[name]=@tab
AND C.[name]=@col

SET @sql='ALTER TABLE '+@tab+' DROP CONSTRAINT '+@name

SELECT @sql
EXEC sp_sqlexec @sql

Kategoria:SQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
chcę dodać kolumnę, która będzie połączeniem dwóch innych istniejących już kolumn, jak powinien wyglądać scrypt?
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!