SQL Server automatic naming of indexes when creating tables

ado, sql-server

Solution

If you don't want the system default names - then just specify your own! :

 CREATE TABLE GENGITFL 
    (
      NR INT NOT NULL IDENTITY,
      GJM CHAR(3) NOT NULL,
      HEITI VARCHAR(25) NOT NULL,
      KAUPG REAL NULL,
      SOLUG REAL NULL,
      TOLLG REAL NULL,
      DUMMY VARCHAR(20) NULL,
    CONSTRAINT IDX_GENGITFL_NR UNIQUE (GJM),
    CONSTRAINT IDX_GENGITFL_GJM PRIMARY KEY (GJM)
    )

See those `CONSTRAINT (yourownnamehere)` before the `UNIQUE` and the `PRIMARY KEY` ?

Now, your constraints are named as you defined.

Problem

I'm rather new to SQL Server, (did learn SQL back in late 1980's, DB2 if I recall) Today I'm integrating my database layer into SQL, to begin with, SQL Server. To begin with. As I do today, I will generate in runtime every databases objects, tables objects and indexes programmatically as I do with almost every visual and data object in my projects. That is, I use the visual designing tools very limited. Every column in my project has a external description file's (every user has profile which contains these files), just as I do with database key's and for visual objects as for effect's as positioning, length, picture-mask, font size, etc. etc. i.e. dynamic forms. Almost every window, grids, filters is created in runtime just as far most of my database connections. I did build a small test "machine" to create tables in this environment and did well, very easy to create tables within program (I use delphi and ADO) The problem I encounter is when I flag a column as "autoincrement" or as `Identity` in SQL Server or if I describe a column as primary key, then SQL Server Management Studio creates automatically a index or key. That would be ok if I could manage the name it gives this index or key. Example of this situations: ``` AdoCommand.CommandText := Str_SQL; TRY AdoCommand.Execute; FINALLY NotiFy_TimeOut ('Table was created', wait_for_one_sec); END; ``` My database engine creates this SQL script which I pass into the string `Str_SQL` above: ``` CREATE TABLE GENGITFL ( NR INT NOT NULL IDENTITY, GJM CHAR(3) NOT NULL, HEITI VARCHAR(25) NOT NULL, KAUPG REAL NULL, SOLUG REAL NULL, TOLLG REAL NULL, DUMMY VARCHAR(20) NULL, UNIQUE (GJM), PRIMARY KEY (GJM) ) ``` SQL Server creates these two indexes automatically : - `PK__GENGITFL__C51F17260A463F49` - `UQ__GENGITFL__C51F17277FA3E6E6` I don't want to use these names for these files, I would prefer names as: - `IDX_GENGITFL_GJM` - `IDX_GENGITFL_NR` The reason should be obvious in light of my intro, the runtime engine can't create these names automatically and I consider it not a option to look for what index files are available within system database. If my external description say there should be index, I would like just to create names for the index automatically by using the prefix, `IDX_` next the table name and last the field name or name's with underscore between, as `IDX_GENGITFL_GJM` etc. Hope someone understand my poor english and presentation.. I'm rather rusty in english. Thanks Edit: After help from marc_s my SQL "script" is like this: ``` CREATE TABLE GENGITFL ( NR INT NOT NULL IDENTITY, GJM CHAR(3) NOT NULL, HEITI VARCHAR(25) NOT NULL, KAUPG REAL NULL, SOLUG REAL NULL, TOLLG REAL NULL, DUMMY VARCHAR(20) NULL, CONSTRAINT IDX_GENGITFL_NR UNIQUE (NR), CONSTRAINT IDX_GENGITFL_GJM PRIMARY KEY (GJM), ) CREATE INDEX IDX_GENGITFL_HEITI ON GENGITFL (HEITI) ``` Thanks again.

Original source