SQL Server : multiple column key lexicographical order

Question

I have a two columns in database that are set as primary key let's say Code1 and Code2. I use Entity Framework fluent api to create key like this:

entity.HasKey(p => new { p.Code1, p.Code2 }).HasName("table_pkey");

What I need is for key to be in lexicographical order. So for example when I have a record in database.

var code1 = "AA01";
var code2 = "AB01";
var pkey = "AA01AB01";

and I try to add

var code1 = "AB01";
var code2 = "AA01";
var pkey = "AB01AA01";

I want this to be regarded as a duplicate key for database so it won't allow to add them.

Thank you for your help

Answer 1

'AA01AB01' and 'AB01AA01' are not equal to each other, so you can't enforce a primary key to be violated here. It seems that what you actually needed here is 2 contraints; the 1st to ensure the value of Code1 is less than Code2 and then the primary key constraint. In T-SQl, this would be defined as the following:

CREATE TABLE dbo.YourTable (Code1 char(4) NOT NULL,
                            Code2 char(4) NOT NULL,
                            pkey AS Code1 + Code2 PERSISTED);
GO
ALTER TABLE dbo.YourTable ADD CONSTRAINT CK_CodeOrder CHECK (Code1 < Code2);
GO
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY NONCLUSTERED (Pkey); --As this doesn't doesn't appear to be always ascending
GO

INSERT INTO dbo.YourTable (Code1,Code2)
VALUES('AA01','AB01');
GO
INSERT INTO dbo.YourTable (Code1,Code2)
VALUES('AB01','AA01'); --Fails as Code1 is greater than Code2
GO

In your application, you likely want to check that Code1 is also less than Code2 and "swap" the values if they aren't.