Uniqueidentifier with NEWSEQUENTIALID as Primary Key

Related products: Software Factory

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.
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?




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.
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
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]'

Jasper,



Great! I will try this.

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

 

 

 

 


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 idea 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.

 

 


Ok, great! I hope it becomes available soon.


In version 2021.1 and up you can specify newid() as the default expression for an uniqueidentifier:

https://docs.thinkwisesoftware.com/docs/sf/data_model.html#default-value

We’ll keep this idea open as newsequentalid() is not yet supported. I’ve updated the title of this idea accordingly.


Updated idea status Working on it!On the backlog