Instantly share code, notes, and snippets.

Embed
What would you like to do?
Change auto-named SQL constraint
-- Table: Users
-- Column: Age
-- Constraint: auto-generated, e.g. DF__Users__Age__<random>
-- Find a specific constrait name assuming there is a single constraint only
DECLARE @ConstraintName nvarchar(100)
SELECT @ConstraintName = c.name FROM sys.default_constraints c
INNER JOIN sys.columns col ON col.default_object_id = c.object_id
WHERE c.parent_object_id = OBJECT_ID('dbo.Users') AND col.name = 'Age'
-- Drop the constraint if found
IF @ConstraintName IS NOT NULL
BEGIN
EXEC('ALTER TABLE [dbo].[Users] DROP CONSTRAINT ' + @ConstraintName)
END
-- Add a new constraint with a predefined name
ALTER TABLE [dbo].[Users] ADD DEFAULT ((0)) FOR [Age]
-- Blog post: https://ignas.me/tech/change-auto-named-sql-constraint/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment