CODEWITHSUNDEEP
sqlsql-serverconstraintsalter-table
Melanie Journe
Melanie Journe

Reputation: 1379

SQL - Cannot add check constraint without checking the existing rows

I need to modify an existing constraint. I read on multiple topic that it's impossible, we need to delete and recreate it. So I've delete it and rewrote it. When trying to save it in SQL Server Management studio I got this error message :

Unable to add constraint chk_AnneeNaissance. The ALTER TABLE statement conflicted with the CHECK CONSTRAINT chk_AnneeNaissance.

I know that I have some row in my database not fulfilling this constraints but I want to add it for the next insert. I've looked around and I found that I need to do the command myself with a NOCHECK so I'm trying this command :

ALTER TABLE [GENERAL].[dbo].[Base] WITH NOCHECK ADD CONSTRAINT chk_AnneeNaissance

But I have this error :

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'chk_AnneeNaissance'.

Can someone help me writing the right command?

Upvotes: 1

Views: 3495

Answers (1)

Melanie Journe
Melanie Journe

Reputation: 1379

ALTER TABLE [ERI_COPY_20170315].[dbo].[Base] WITH NOCHECK
ADD CONSTRAINT chk_AnneeNaissance
CHECK (
[Année de naissance] IS NOT NULL AND 
[Année de naissance]>=([Date de valeur]-(80)) AND 
[Année de naissance]<=([Date de valeur]-(15)) AND
len([Année de naissance])=(4)
)

It works perfectly. Thanks James Z ! I didn't know where I should put my constraints code.

Upvotes: 4

Related Questions