Forum Discussion

robertrackl's avatar
robertrackl
Copper Contributor
Nov 06, 2021

Unusual uniqueness requirement on two fields in a table

I have a table called SF_ACCOUNTS that represents the chart of accounts in a bookkeeping system. Among the fields of the table are sName and sAltName, the name of the account and an alternate name. I...
  • robertrackl's avatar
    robertrackl
    Nov 12, 2021

    LainRobertson 

    I think I've got it now. Thanks for your inspiration!

    CREATE TABLE dbo.test
    (sName nvarchar(128) NOT NULL, sAltName nvarchar(256) NOT NULL)
    GO
    
    CREATE UNIQUE NONCLUSTERED INDEX [IX_test_sName] ON [dbo].[test] ([sName] ASC)
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [IX_test_sAltName] ON [dbo].[test] ([sAltName] ASC)
    GO
    
    CREATE FUNCTION dbo.bNameXAlt (@sName AS nvarchar(128), @sAltName AS nvarchar(256))
    RETURNS bit
    AS
    BEGIN
    	IF @sName = @sAltName  RETURN 0
    	IF
    		EXISTS (SELECT sName FROM dbo.test AS [t] WHERE [t].sName = @sAltName)
    		OR
    		EXISTS (SELECT sAltName FROM dbo.test AS [t] WHERE [t].sAltName = @sName)
    		RETURN 1
    	RETURN 0
    END
    GO
    
    ALTER TABLE dbo.test
    	ADD CONSTRAINT sNameNotInAltNames CHECK (dbo.bNameXAlt(sName, sAltName) = 0)
    GO

    It does what I wanted to achieve.

     

Resources