Unusual uniqueness requirement on two fields in a table

[robertrackl]

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 need the entries in sName to be unique, and I need the entries in sAltName to be unique with this additional requirement:

• Any sName must not match any sAltName except when they are in the same record.

Is there a way of setting up an index / constraint / whatever for this table to fulfill these requirements?

[#2]

Hi @robertrackl --  You should be able to create a few unique constraints to address your requirements.  Below is an example based loosely on the information you provided regarding your table.

Here is my SF_ACCOUNTS table

CREATE TABLE SF_ACCOUNTS (AccountID smallint identity(1,1),sName nvarchar(128), sAltName nvarchar(128))

I'll create the following unique constraints:

1.  One unique constraint for the sName

2.  One unique constraint for the sAltName

3.  One unique constraint for the combination of AccountID, sName, sAltName 

ALTER TABLE SF_ACCOUNTS ADD CONSTRAINT UniquesName UNIQUE(sName)
ALTER TABLE SF_ACCOUNTS ADD CONSTRAINT UniquesAltName UNIQUE(sAltName)
ALTER TABLE SF_ACCOUNTS ADD CONSTRAINT validNames UNIQUE (AccountId,sName,sAltName)

And test using somethin like what is shown below:

-- This is valid
INSERT INTO SF_ACCOUNTS VALUES ('an sname','an saltname')
-- This is valid
INSERT INTO SF_ACCOUNTS VALUES ('an sname with the same saltname','an sname with the same saltname')
-- This is not valid
INSERT INTO SF_ACCOUNTS VALUES ('an sname','an saltname')
-- This is not valid
INSERT INTO SF_ACCOUNTS VALUES ('a new sname','an saltname')

Hope that helps.  Take care.

[#3]

@robertrackl , @bake13 

I might be missing something in the requirement, but looking at the third constraint there, bake, that's not quite going to preclude an sAltName featuring in the sName column.

For example, the following will pass the third constraint where - again, so long as I've understood the requirement correctly - it shouldn't:

sName	sAltName
an sname	an saltname
an saltname	something else

The first two constraints are necessary but I've gone with a function to satisfy the robert's bullet-pointed requirement (i.e. as the third constraint). This throws the expected constraint violation when using the above bogus data.

Let me know your thoughts.

CREATE TABLE dbo.test
(
	sName varchar(max) NOT NULL
	, sAltName varchar(max) NOT NULL
)
GO


CREATE FUNCTION dbo.SNameInAltName (@sName AS varchar(max))
RETURNS int
AS
BEGIN
	IF EXISTS (SELECT sAltName FROM dbo.test AS [t] WITH (nolock) WHERE [t].sAltName = @sName) 
		return 1;

	return 0;
END
GO


ALTER TABLE dbo.test
	ADD CONSTRAINT sNameNotInAltNames CHECK (dbo.SNameInAltName(sName) = 0)
GO

[#4]

@LainRobertson , @bake13 

Building on @LainRobertson 's ideas, this is what I have come up with (it prevents repeating something across the sName and sAltName columns, but it also prevents sName and sAltName being the same in the same record which I am trying to allow):

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.SNameInAltName (@sName AS nvarchar(128))
RETURNS bit
AS
BEGIN
	IF EXISTS (SELECT sAltName FROM dbo.test AS [t] WITH (nolock) WHERE [t].sAltName = @sName) 
		return 1;
	return 0;
END
GO

CREATE FUNCTION dbo.SAltNameInName (@sAltName AS nvarchar(256))
RETURNS bit
AS
BEGIN
	IF EXISTS (SELECT sName FROM dbo.test AS [t] WITH (nolock) WHERE [t].sName = @sAltName) 
		return 1;
	return 0;
END
GO

ALTER TABLE dbo.test
	ADD CONSTRAINT sNameNotInAltNames CHECK (dbo.SNameInAltName(sName) = 0)
GO
ALTER TABLE dbo.test
	ADD CONSTRAINT sAltNameNotInNames CHECK (dbo.SAltNameInName(sAltName) = 0)
GO

Can someone help with the last requirement that sName = sAltName in the same record should be allowed?

[#5]

@robertrackl 

Sticking with the original three constraint approach, you can just alter the existing function to accommodate this requirement. A second function isn't necessary to achieve this.

Here's an example for dropping the constraint, updating the function and putting it back. Just change things like the tables names to suit your environment.

-- Drop the existing constraint so the function can be updated.
ALTER TABLE [dbo].[test] DROP CONSTRAINT [sNameNotInAltNames]
GO

-- Alter the function.
ALTER FUNCTION [dbo].[SNameInAltName] (@sName AS varchar(max))
RETURNS int
AS
BEGIN
	IF EXISTS (SELECT sAltName FROM dbo.test AS [t] WITH (nolock) WHERE [t].sName <> @sName AND [t].sAltName = @sName) 
		return 1;

	return 0;
END
GO

-- Put the constraint back in place.
ALTER TABLE [dbo].[test]  WITH CHECK ADD  CONSTRAINT [sNameNotInAltNames] CHECK  (([dbo].[SNameInAltName]([sName])=(0)))
GO

[#6 · Accepted solution]

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

[#7]

@robertrackl 

Glad to hear it, mate!

In the interest of efficiency, you might want to consider cutting the two queries inside the function down to one:

ALTER FUNCTION [dbo].[SNameInAltName] (@sName AS varchar(max), @sAltName AS varchar(max))
RETURNS int
AS
BEGIN
	IF (@sName <> @sAltName) AND EXISTS (SELECT 1 FROM dbo.test AS [t] WITH (nolock) WHERE [t].sName = @sAltName OR [t].sAltName = @sName)
		return 1;

	return 0;
END