How to Automatically Name a CONSTRAINT Manually Created in SQLServer 2016

Question

How to Automatically Name a CONSTRAINT Manually Created in SQLServer 2016

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

Hello, I'm creating a database and I need to create some compound indexes to optimize the bank's processing. The problem is that I do not want to have to name those indexes one-by-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 the CONSTRAINT for me for the email (UNIQUE) and password (DEFAULT) fields. The problem is when I have to create a compound 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 I can use in declaring this index so that it is automatically named? or any other way to create this composite index so it has the same result?

sql sql-server tsql

asked by anonymous 22.08.2018 / 17:53

2 answers

1

Not if you want to use the command create index , the name is required: 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 takes care of naming, but if you create it explicitly using the create command you need to give it a name.

22.08.2018 / 18:43

Is it a good idea to use JSON as a database? Verify if border is applied

score 1 · Accepted Answer

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

However, you can create more complex constraints already in table creation, so you do not have to name them manually. Considering 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% comp_constraint that checks two columns of the table or even a compound foreign key.