How to automatically name a manually created CONSTRAINT in Sqlserver 2016

Question

Hello, I am creating a database and need to create some compound Index to optimize the bank processing. The problem is that I don’t want to have to name these Indexes one-on-one. Normally, I create a table as follows:

CREATE TABLE Usuario (
    id INT NOT NULL IDENTITY PRIMARY KEY,
    email NVARCHAR(100) NOT NULL UNIQUE,
    senha NVARCHAR(100) NOT NULL DEFAULT '123',
)

When creating this table, Sqlserver automatically generates for me the CONSTRAINT for the email (UNIQUE) and password (DEFAULT) fields. The problem is when I have to create a composite index, as in the example below:

CREATE TABLE Foo (
    id INT NOT NULL IDENTITY PRIMARY KEY
);
CREATE TABLE Bar (
    id INT NOT NULL IDENTITY PRIMARY KEY
);
CREATE TABLE Bin (
    id INT NOT NULL IDENTITY PRIMARY KEY,
    FooId INT NOT NULL FOREIGN KEY REFERENCES Foo(id),
    BarId INT NOT NULL FOREIGN KEY REFERENCES Bar(id)
);
CREATE UNIQUE INDEX [UX_Bin_FooIdBarId] ON Bin(FooId, BarId);

Is there any form or statement or wildcard character that I can use in the declaration of that index for it to be named automatically? or else some other way to create this composite index so that it has the same result?

Answer 1

Not if you want to use the command create index, the name is mandatory: Docs.microsoft.com

What happens as you already know is that when you use create table or alter table and create constraints or indexes (primary key for example) implicitly (using default, Unique, etc), the sql-server is in charge of naming, but if creating explicitly using the create command you need to give a name.

Answer 2

As Ricardo Pontual commented, it is mandatory to inform the name of constraints created outside the command CREATE TABLE.

However, it is possible to create more complex constraints when creating the table, so you do not need to name them manually. Consider your example:

CREATE TABLE Bin (
  id INT NOT NULL IDENTITY PRIMARY KEY,
  FooId INT NOT NULL FOREIGN KEY REFERENCES Foo(id),
  BarId INT NOT NULL FOREIGN KEY REFERENCES Bar(id),
  UNIQUE(FooId, BarId)
);

Remembering that you can also use this statement to define other constraints or indexes on the table, such as a CHECK Constraint checking two table columns or even a composite foreign key.