In T-SQL, it is usually best to name all constraints you create, including check constraints.

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

If you do not provide an explicit name (myconstraint), then the server will generate you a unique name that is not special in the error message readable.

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

If ImyconstraintIn the T-SQL abovethe name is omitted , this is a syntax error.

The reason I ask is to check constraints in tempdb.Temporary table names are per session, so calling your table is no problem#x.You can use this name in multiple different programs (or multiple entities of the same program running at the same time) without them conflicting.Only global temporary tables (as ##x) needs to have a globally unique name.

However, constraint names must be unique within tempdb and not per session.So if you give them a readable name, you run the risk of colliding with the same name in other connections.You need to do something to make it globally unique, either paste some junk on the client side, or use dynamic SQL.I would very much prefer not to specify a name and let the server handle the job of naming constraints, as it already happened when I created a unique index on a temp table.

How to make a check constraint without specifying a name?

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

uj5u.com enthusiastic netizens replied:

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.

uj5u.com enthusiastic netizens replied:

When you explicitly create a CONSTRAINTyouRequiredSpecify a name.When youCONSTRAINTImplicitWhen creating one , you get a name 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 4 CONSTRAINT s.These constraints are:

• Primary Key ConstraintID
• default constraints SomeDate
• Default constraints on SomeInt,
Check constraints on
• SomeInt.

We can check this by looking at sysobjectto verify this:

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 run it, the generated name is: p>

Constraint Type	Constraint name
Key constraints	PK__MyTable__3214EC27B4E6D2B3
default constraints	DF__MyTable__SomeDat__6A33284E
Check Constraints	CK__MyTable__SomeInt__6B274C87

---

This does not mean that you are creating CONSTRAINTTable DDL A name cannot be provided explicitly.If you want to define them as part of a column 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));