Working on it!

Uniqueidentifier as Primairy Key, with NEWID or NEWSEQUENTIALID as default

  • 3 September 2019
  • 8 replies
  • 181 views

Userlevel 6
Badge +14
I like to use a domain with Uniqueidentifier as datatype for primairy key fields in tables.

In this case the pk field of new records should get a the value of function NEWID() or NEWSEQUENTIALID().

Using NEWID can be achieved by using a default procedure, but using NEWSEQUENTIALID() as default value in the table definition will result in better performance.

I think this requires adjustments in the SF.

8 replies

Userlevel 6
Badge +14

Ok, great! I hope it becomes available soon.

Userlevel 6
Badge +8

Hi Harm,

We have fixed this for the UNIQUEIDENTIFIER datatype, but not yet for datatypes that are marked Calculated, like ROWGUID and ROWVERSION.

We’re currently working on a community idee that will make it possible to specify a SQL expression or function as the default value for a column, so you can use the same UNIQUEIDENTIFIER domain for both the primary keys and foreign keys. 

This way you won’t need to “abuse” the ROWGUID datatype anymore, which was originally introduced to support SQL Server Filestreams.

 

 

Userlevel 6
Badge +14

Hi Jasper,

The fix you mentioned still doesn't seem to have been implemented.

I am trying to set a field of type ROWGUID as Primairy Key, but TSF still doesn’t accept that.

The workaround proposed by you works well

 

 

 

 

Userlevel 6
Badge +14
Jasper,

Great! I will try this.
Userlevel 6
Badge +8
Hi Harm,

Thanks for submitting the issue. We'll investigate the problem as soon as possible.

Regarding the primary key: you can manually update the primary key column using a query on the SF while we're working on a fix.

code:
update col 
set primary_key = 1
where project_id = '[project]'
and project_vrs_id = '[project_version]'
and tab_id = '[table]'
and col_id = '[column]'
Userlevel 6
Badge +14
I found another problem, actually I think it is a bug.

The reference between a master and detail table is not working when the master table field has a domain with datatype ROW_GUID and the detail table field has a datatype UNIQUEIDENTIFIER.

I will make an issue for this in TCP
Userlevel 6
Badge +14
This is almost the solution, which I am looking for. But not yet, because the SF does not allow fields with this type of domain to be Primairy Key.

Hopefully this can be made available in a next version.
Userlevel 6
Badge +8
Hi Harm,

Using the DTTP properties field, it already is possible to add extra keywords to the column definition. We use this, for example, for the ROWGUID data type required for filestreams. Would this work for you?

Reply