SQL SERVER – How to ALTER CONSTRAINT

After reading my earlier blog post SQL SERVER – Prevent Constraint to Allow NULL. I recently received question from user regarding how to alter the constraint.

No. We cannot alter the constraint, only thing we can do is drop and recreate it.

Here is the CREATE and DROP script.

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable (ID INT, Col1 INT, Col2 INT)
GO
-- Create Constraint on Col1
ALTER TABLE TestTable ADD CONSTRAINT CK_TestTable_Col1
CHECK (Col1 > 0)
GO
-- Dropping Constraint on Col1
ALTER TABLE TestTable DROP CONSTRAINT CK_TestTable_Col1
GO
-- Clean up
USE MASTER
GO
ALTER DATABASE TestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE TestDB
GO

If you try to alter the constraint it will throw error.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – How to Use Decode in SQL Server?
Next Post
SQLAuthority News – 1700th Blog Posts – Over 25 Millions of Views – A SQL Milestone

Related Posts

13 Comments. Leave new

  • Hello Pinal,

    I want to make a table, with expression a coloumn_id combination varchar and identity int.
    example :
    CREATE TABLE [dbo].[TestTableCity]
    (
    [City] AS (‘City’+CAST(IDENTITY(1,1)) AS VARCHAR(50)))
    ,CityName VARCHAR(50)
    )

    please check my query, help help

    Reply
    • CREATE TABLE [dbo].[TestTableCity]
      (
      Id int identity(1,1),
      [City] AS (‘City’+CAST(Id VARCHAR(50)))
      ,CityName VARCHAR(50)
      )

      Reply
  • Imran Mohammed
    April 26, 2011 5:34 am

    @Sbn

    I don;t see a reason why you have to do concatination in table, you can always do this in view that does a select on this table.

    For Ex:

    CREATE TABLE [dbo].[TestTableCity]
    (
    ID int IDENTITY(1,1) not null
    ,CityName VARCHAR(50)
    )

    CREATE VIEW [Vw_TestTableCity]
    AS
    Select ‘City’+ Convert(varchar(46),ID) AS City
    ,CityName
    From [TestTableCity]

    go

    Insert into TestTableCity values (‘Hyderabad’)

    go
    Select * from [Vw_TestTableCity]

    But if you want to do it your own way which I don;t recommend, below is a way,

    CREATE TABLE [dbo].[TestTableCity]
    (
    [City] AS (‘City’+ Convert(varchar(46),ID))
    ,CityName VARCHAR(50)
    ,ID int IDENTITY(1,1) not null
    )
    go
    Insert into TestTableCity values (‘Hyderabad’)
    go
    Select * from TestTableCity

    ~ IM.

    Reply
  • Nakul Vachhrajani
    April 26, 2011 11:11 pm

    Hello!

    It would be nice to understand the reason why SQL Server does not allow us to modify, i.e. alter an already established constraint.

    Any plans on writing an article on the same?

    Reply
  • We can prevent constraints…….

    create table x
    (
    id int not null,
    salary money not null constraint check_sale check(salary>0)
    )
    select * from x
    insert into x values (1,-8)
    alter table x nocheck constraint check_sale

    Reply
  • how to apply constraint for valid email address in oracle

    Reply
  • how to remove constraint which i dont gave the constraint name my syntax is
    CREATE TABLE emp
    (
    e_id NUMBER,
    first_name VARCHAR2 (15),
    last_name VARCHAR2 (15),
    doj DATE,
    salary NUMBER (10),
    d_id NUMBER (3),
    l_id NUMBER (3),
    week_off VARCHAR2 (15) NOT NULL,
    mngr_id NUMBER (3),
    pan_no VARCHAR2 (10) UNIQUE,
    phone_no NUMBER (20)
    )
    i want remove the mngr_id constraint
    plz reply

    Reply
  • Hi Pinal Dave,
    awesome articles, I like’s the way you have explain things.

    Reply
  • Hi Sir
    I am a novice to Sql. I find your blogs very hyelpful

    Thanks,
    Chhaya

    Reply
  • hola tengo un problema con sql 2014 no me permite borrar la llave primaria (ALTER TABLE table
    DROP CONSTRAINT campo ) ni tampoco de esta manera (ALTER TABLE table
    DROP PRIMARY KEY campo ) me gustaria que me puedas ayudar porfavor

    Reply

Leave a Reply

Menu