CODEWITHSUNDEEP
sql-server.net-coreentity-framework-coreprimary-keylexicographic-ordering
Teamol
Teamol

Reputation: 819

SQL Server : multiple column key lexicographical order

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

Upvotes: 0

Views: 176

Answers (1)

Thom A
Thom A

Reputation: 95659

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

Upvotes: 1

Related Questions