0

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)
Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, that was it. If you remove just the word myconstraint it's a syntax error, but remove constraint myconstraint and it works.
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));
Improve this answer

7 Comments

I don't understand what you mean by explicit and implicit. The answer from sticky bit shows how to explicitly create a constraint with an automatic name. It doesn't have to be done as part of the create table.
CREATE CONSTRAINT... You are explicitly creating a CONSTRAINT, @EdAvis .
@EdAvis - when you specify the CONSTRAINT key word you must also specify a name
As Martin said, when you add the CONSTRAINT key word, you are being explicit about what you are creating; At that point you have to therefore define the name. If you omit the CONSTRAINT keyword (which I am therefore saying you are implicitly creating it, rather than being explicit it's a CONSTRAINT) you don't need the name. Apologies if my terminology was ambiguous, @EdAvis .
Correct, @EdAvis . This is why I mentioned being explicit about making a CONSTRAINT. In the first example you can see that I omit CONSTRAINT in all cases, and so all them CONSTRAINTs created have automatically generated named. On the other hand, in the latter example they all explicitly state CONSTRAINT, which therefore must be followed by the name of said CONSTRAINT.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.