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.

Dário Santos
  • 13
  • 1
  • 4
  • 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 00: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 05:09

2 Answers2

8

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'.

David Browne - Microsoft
  • 46,306
  • 3
  • 46
  • 96
  • What do you mean when you say "(not a constraint)", that it is not stored in the catalog together with other constraints? – Lennart - Slava Ukraini May 25 '18 at 04: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 07:21
  • 1
    @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 09: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.

KumarHarsh
  • 1,648
  • 11
  • 10