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