Sql-server – Can default object names be changed

sql server

Sooner or later, a db developer will come across, and possibly use, a naming scheme for many objects like foreign keyes, default constraints etc.

Let's consider this example: Name all foreign keys as:
FK__[name of source table]__[name of source col]__ref__[name of master table]__[name of master column]

What kind of parametrization/external programs could be used to achieve having such a name as default, when for example using a create table statements which mentions the fk but does not explicitly provide a constraint name?

Best Answer

What kind of parametrization/external programs could be used to achieve having such a name as default, when for example using a create table statements which mentions the fk but does not explicitly provide a constraint name?

There's no way to change the default name generated when the DDL doesn't contain a name. The only thing you can do is to run a batch process to identify non-compliant objects and perhaps drop and recreate them.

Related Solutions

Sql-server – non-DDL way to preserve back-references when staging data into production

I would suggest adding a (set of) staging tables to the destination or an intermediary that can be better controlled and more stable. Place the tracking info there. Then do all the transformation from the staging to the final destination, either carrying the tracking info with it or discarding it.

There are several ways you can generate a tracking key from multiple systems, as long as they all follow the same algorithm, it doesn't have to be an INT. It could be a 2 char prefix with a sequential number. For that matter it doesn't have to be just one column.

Sql-server – With MS SQL Server are the generated constraint names predictable

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar) 
  REFERENCES dbo.foo(bar));

Best Answer

Related Solutions

Sql-server – non-DDL way to preserve back-references when staging data into production

Sql-server – With MS SQL Server are the generated constraint names predictable

Related Question