MSSQL 2008 Autoincrement Identity

Toad Data Modeler
1

I have another issue with MSSQL 2008. I have a primary key in a given table and it should be filled by autoincrement values. so the identity check box is true. when I look at the sql script, the foreig key has the same identity statement as the primary key. is that the way it should work ?

Ikrischer

2

Hi Ilja,

I created new model for MS SQL 2008, added there few entities and tried to simulate your case, but the generated code seems to be OK. How was your model created? Reverese engineered, converted or made from scratch?

Please send us the model to modeling(at)quest.com and write us what code is wrong.
Thank you,

Vaclav

3

I am using 3.5.10.4 and the model was made from the scratch. here is an example code from the generated DDL script. As you can see the IDENTITY keyword is used at the foreign key column as well and should only be used at the primary key

CREATE TABLE [LegalEntity]
(
[PK_LegalEntity] Bigint IDENTITY NOT NULL,
[FK_LegalEntity] Bigint IDENTITY NULL,
[AK_LegalEntity] Varchar(32) NOT NULL,
[LegalEntityName] Varchar(32) NOT NULL,
[EK_LegalEntityType] Varchar(32) NOT NULL,
[AddressLine1] Varchar(64) NULL,
[AddressLine2] Varchar(64) NULL,
[ZipCode] Varchar(16) NULL,
[City] Varchar(32) NULL,
[Country] Varchar(32) NULL,
[Website] Varchar(128) NULL,
[GroupID] Varchar(32) NULL,
[CommercialRegistryID] Varchar(32) NULL,
[TaxID] Varchar(32) NULL,
[ValueAddedTaxID] Varchar(32) NULL,
[Phone1] Varchar(32) NULL,
[Phone2] Varchar(32) NULL,
[Fax] Varchar(32) NULL,
[Email] Varchar(128) NULL
)
go

– Create indexes for table LegalEntity

CREATE INDEX [LegalEntity_FK_LegalEntity] ON [LegalEntity] ([FK_LegalEntity])
go

– Add keys for table LegalEntity

ALTER TABLE [LegalEntity] ADD CONSTRAINT [PK_LegalEntity] PRIMARY KEY NONCLUSTERED ([PK_LegalEntity])
go

ALTER TABLE [LegalEntity] ADD CONSTRAINT [LegalEntity_AK_LegalEntity] UNIQUE ([AK_LegalEntity])
go

I think I know the reason for that behaviour. I made an own Domain Type for the primary keys with the Identifier option, so I can use the same data type for all of my primary keys. of course the foreigh key columns should have the same data type so the same domain. but that makes no sense to me to create IDENTITY keyword on columns, that are not signed as a primary key.

I could assign the IDENTITY on table layer, but would be more nice to do it at the domain definition layer. And then it would be nice to check, if I have a primary key or a foreign key and use the IDENTITY keyword only for priamry keys.

Ilja

Message was edited by: Ilja

4

Hello Ilja,

yes, it is exactly as you write. If a domain is in PK attribute, it is also used in migrated FK attribute and therefore also Identity is used.

There are two possible solutions:

  1. Not to use Identity in domain but use Identity in FK attribute (to which the domain has been assigned).
  2. Use Identity in domain as you do it now, but then open the FK attribute and on tab Identity select the Override Identity Property checkbox. - You can use a script or macro to do it automatically. Please find the script attached.

If you have any questions, please write us back. Thanks.

Regards,

Vladka + TDM Team
FKIdentityOverride.txt (1004 Bytes)

5

Hello Vladka,

thanks for that info. in both cases, I have to make a least one extra step for every IDENTITY field in my database (besides I use your script at the end). But what I am looking for is to create one domain for everyprimary key field inclusive the IDENTITY option without having it in the foregin key. And I can see no useful case where the behaviour like it is now would be wanted. So my question is, will it be changed in the future, I can create one primary key domain with the IDENTITY option without having the IDENTITY key word in the foreign key ?

Ilja

6

Hello Ilja,

Thanks. We will consider your request. At the moment I’m not able to promis that it will be changed.

Thanks for your patience.

Regards,

Vladka + TDM Team