SQL Server automatic naming of indexes when creating tables

Question

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.

Answer 1

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.