1

is there any difference between the follow exemples?

ProjNum int 
    CONSTRAINT nn_ProjNum NOT NULL 
        CONSTRAINT C_ProjNum CHECK (ProjNum >= 10)

and

ProjNum int NOT NULL 
    CHECK (ProjNum >= 10)

Thanks.

  • I could be wrong but I think the difference here is that a CHECK constraint limits the values that can be accepted by a column where CONSTRAINT limits the values that can be accepted in a table. – krebshack May 24 '18 at 22:41
  • 1
    There is no difference in SQL Server between those two statement. Each results in NOT NULL column (not a constraint) with a single check constraint. The only difference is that second one results in a system-named check constraint, something like 'CK__t__ProjNum__4AB81AF0'. – David Browne - Microsoft May 25 '18 at 0:48
  • If you can, avoid at all costs system named constraints! A user ringing support with an error containing the string 'CK__t__ProjNum__4AB81AF0 is likely to be waiting a while ("How many underscores... err... spell that again please...") whereas with an error like "stock_gt_zero_ck" is much more likely to get a fast (and correct) response! Ahh... the good old days with Oracle system named constraints... <nostalgic tear runs down cheek... :-) ). – Vérace May 25 '18 at 5:09
6

There is no difference in SQL Server between those two statements. Each results in NOT NULL column (not a constraint) with a single check constraint. The only difference is that second one creates a system-named check constraint, something like 'CK__t__ProjNum__4AB81AF0'.

  • What do you mean when you say "(not a constraint)", that it is not stored in the catalog together with other constraints? – Lennart May 25 '18 at 4:53
  • Hope David don't mind me jumping in... NOT NULL isn't implemented with the "constraint syntax" in SQL Server, You just say NULL and NOT NULL. I believe that this is what David means when he says that NOT NULL isn't a constraint. It does constrain what data you can have in the table of course (you can't have NULL. But the language implementation isn't according to the other constraints (PK, FK, CHECK, ...). I believe that in ANSI SQL, you can describe NOT NULL using the "constraint syntax" and just saying NOT NULL is a shorthand. But don't hold me on that. :-) – Tibor Karaszi May 25 '18 at 7:21
  • @TiborKaraszi: SQL Server does support the CONSTRAINT name NOT NULL syntax, though (for compatibility with other products, perhaps). Of course, given that NOT NULL is a column property rather than a constraint in SQL Server, the name is just ignored. – Andriy M May 29 '18 at 9:19
0

Both are same.

In my understanding ,CONSTRAINT CHECK can be define when you add new columns.

ALTER TABLE dbo.DocExc   
   ADD ColumnD int NULL   
   CONSTRAINT CHK_ColumnD_DocExc   
   CHECK (ColumnD > 10 AND ColumnD < 50);  
GO

Also user can provide own constraint name.

But if we have to put check in existing column then

 ALTER TABLE dbo.DocExc   
 ADD  CHECK (ExistsColumn > 10 AND ExistsColumn < 50);  
 GO

system will provide constraint name here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.