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. Sadly, there is no ALTER CONSTRAINT command in SQL Server.
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.
How to ALTER CONSTRAINT Safely When There Is No Such Command
Drop and recreate sounds simple, but on a live table there is a short window where no rule protects your data. Wrap both steps in one transaction. Nobody can slip a bad row in between, and if the new definition fails, the rollback brings the old one back. Keep that transaction short, because the table stays locked until it commits.
Keep in mind that a new constraint checks the existing rows by default. If old data breaks the new rule, the ADD step fails. Find those rows first with a simple SELECT that uses the opposite condition, fix them, and then create the constraint. For the demo rule, that means looking for rows where Col1 is zero or less.
Sometimes you do not need to change the rule at all, you only need to switch it off for a big data load. For CHECK and FOREIGN KEY constraints you can run ALTER TABLE TestTable NOCHECK CONSTRAINT CK_TestTable_Col1 and turn it back on later. Be careful here: turn it back on with WITH CHECK CHECK CONSTRAINT, so SQL Server validates the rows again. If you skip WITH CHECK, the constraint is marked as not trusted, and the optimizer can no longer rely on it. You can spot those in the is_not_trusted column of sys.check_constraints and sys.foreign_keys.
The same drop and recreate idea applies to primary keys, unique constraints and defaults. For a primary key, you may have to drop the foreign keys that point to it first and add them back afterwards. Always give your constraints clear names, as in the demo, so the drop script is easy to write. Since there is no ALTER CONSTRAINT, that name is your handle on the rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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
CREATE TABLE [dbo].[TestTableCity]
(
Id int identity(1,1),
[City] AS (‘City’+CAST(Id VARCHAR(50)))
,CityName VARCHAR(50)
)
thanks madhivanan, previously I also think the same as your :)
@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.
wow, thanks imran. i am try it
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?
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
how to apply constraint for valid email address in oracle
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
Hi Pinal Dave,
awesome articles, I like’s the way you have explain things.
Hi Sir
I am a novice to Sql. I find your blogs very hyelpful
Thanks,
Chhaya
helpful*
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