Microsoft SQL Server : check constraint with generated name

Question

In T-SQL, it's normally best to name all constraints you create, including check constraints.

alter table mytable with check 
    add constraint myconstraint check (mycol > 0)

If you don't provide an explicit name (myconstraint), then the server will generate a unique name for you, which isn't particularly readable in error messages.

Or so everyone says. But is it even possible to get this generated name for check constraints? I have seen it for foreign key constraints and unique constraints, but I don't know how to create a check constraint without specifying the name.

If I leave out the name myconstraint in the above T-SQL, it's a syntax error.

The reason I ask is for check constraints in tempdb. The names of temporary tables are per-session, so it's no problem to call your table #x. You can have that name in several different programs (or several instances of the same program running concurrently) and they don't clash. Only the global temporary tables (as ##x) need to have globally unique names.

However, constraint names have to be unique within tempdb and are not per-session. So if you give them a readable name, you run the risk of clashing with the same name in other connections. You need to do something to make it globally unique, either pasting in some gunk on the client side or resorting to dynamic SQL. I would greatly prefer to leave the name unspecified and have the server handle the job of naming the constraint, as already happens when I create unique indexes on my temporary tables.

How can I make a check constraint without specifying a name for it?

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)

Answer 1

Maybe your syntax is wrong (you didn't show us). Simply

ALTER TABLE elbat
            WITH CHECK
            ADD CHECK (nmuloc = 1);

should work fine and SQL Server will generate a name.

Answer 2

When you explicitly create a CONSTRAINT then you must specify a name. It is when you are creating a CONSTRAINT implicitly that you get one with an automatic name.

Take the following DDL statement:

CREATE TABLE dbo.MyTable (ID int IDENTITY PRIMARY KEY,
                          SomeDate date DEFAULT GETDATE(),
                          SomeInt int DEFAULT 1 CHECK (SomeInt> 0));

The above creates a table, with 3 columns, but also with four CONSTRAINTs. These constraints are:

• A Primary key constraint on ID
• A default constraint on SomeDate
• A default constraint on SomeInt,
• A Check constraint on SomeInt.

We can validate this by looking at the sys objects:

SELECT N'Key Constraint', [name] AS ConstraintName
FROM sys.key_constraints kc
WHERE parent_object_id = OBJECT_ID(N'dbo.MyTable')
UNION ALL
SELECT N'Default Constraint', [name] AS ConstraintName
FROM sys.default_constraints 
WHERE parent_object_id = OBJECT_ID(N'dbo.MyTable')
UNION ALL
SELECT N'Check Constraint', [name] AS ConstraintName
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(N'dbo.MyTable');

When I ran this, this generated the generated names were:

Constraint Type	Constraint Name
Key Constraint	PK__MyTable__3214EC27B4E6D2B3
Default Constraint	DF__MyTable__SomeDat__6A33284E
Check Constraint	CK__MyTable__SomeInt__6B274C87

---

That doesn't mean you can't explicitly provide a name when creating the CONSTRAINT within the DDL of the table though. If you want to define them as part of the column it would look like this:

CREATE TABLE dbo.OtherTable (ID int IDENTITY CONSTRAINT PK_OtherTable PRIMARY KEY,
                             SomeDate date CONSTRAINT DF_OtherTable_SomeDate DEFAULT GETDATE(),
                             SomeInt int CONSTRAINT DF_OtherTable_SomeInt DEFAULT 1
                                         CONSTRAINT chk_OtherTable_SomeInt CHECK (SomeInt> 0));