Forum Discussion
robertrackl
Nov 06, 2021Copper Contributor
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...
- Nov 12, 2021
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.
robertrackl
Nov 12, 2021Copper Contributor
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?
LainRobertson
Nov 12, 2021Silver Contributor
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
- robertracklNov 12, 2021Copper Contributor
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.
- LainRobertsonNov 12, 2021Silver Contributor
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