Microsoft SQL Server : Check Constraint With Generated Name
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)
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.
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?