mORMot Open Source

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

Unique properties can't be implemented in MS SQL Server

Unique properties as in the following example:

  TSQLGroups = class(TSQLRecord)
    private
      fGroupName: RawUTF8;
      fGroupMember: variant;
    published
      property GroupName: RawUTF8 read fGroupName write fGroupName stored AS_UNIQUE
      property GroupMember: variant read fGroupMember write fGroupMember;
  end;

can't be implemented as virtual tables in MS SQL Server 2017. TSQLRestServerDB.CreateMissingTables sends the following query to SQL Server:

CREATE TABLE dbo.Groups (ID bigint NOT NULL PRIMARY KEY,GroupName nvarchar(max) NOT NULL UNIQUE,GroupMember nvarchar(max))

which raises an error that the 'GroupName' column is of a type that is invalid for use as a key column in an index.

If the 'stored AS_UNIQUE' is removed from the property the Model is implemented without problem.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,656

Website

Re: Unique properties can't be implemented in MS SQL Server

Do you know what is the SQL to be used for MS SQL for an unique column?

Is the following the way to go:

GroupName nvarchar(max) NOT NULL, CONSTRAINT AK_GroupName UNIQUE(GroupName)  

This is weird, because the syntax we produce seems pretty valid.
See https://www.w3schools.com/sql/sql_unique.asp

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: Unique properties can't be implemented in MS SQL Server

IMHO in general unique column validation should be done by creating unique index. Some RDBMS creates unique index automatically, some not. Without unique index constraint validation becomes very slow. Cross DB solution I use is:

Create unique index uidx_tabName_fldName on tabName(fldName)

Constraint not necessary in this case

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

Re: Unique properties can't be implemented in MS SQL Server

I'm afraid that is not a matter of syntax. MS SQL Server doesn't support indices on nvarchar(max) at all. One (manual) workaround is to use a hashed computed column and create an index on it:

alter table Groups 
alter column GroupName nvarchar(max) not null;

alter table Groups
add HashedGrN as cast(hashbytes('SHA2_256', GroupName) as varbinary(256));

alter table Groups
add constraint UC_GroupName unique(HashedGrN);

The above works, but I'm not sure how efficient is it.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: Unique properties can't be implemented in MS SQL Server

Up. I miss nvarchar(max). Actually in this case (GroupName) better to limit columns size using property GroupName: RawUTF8 read fGroupName write fGroupName index 100 stored AS_UNIQUE
It's better from all POW

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

Re: Unique properties can't be implemented in MS SQL Server

Exactly. I confirm that the problem was the absence of column size. Writing the model with index attributes as:

      property GroupName: RawUTF8 index 100 read fGroupName write fGroupName stored AS_UNIQUE;
      property GroupMember: variant index 1024 read fGroupMember write fGroupMember;

works fine, so it was not a problem of a different sql syntax. MS SQL Server apparently supports the syntax used by the framework, as the link from w3schools posted by ab before declares.

Thanks for the hint.