June 24, 2019 at 8:15 am
Basic outline is that I wish to ensure uniqueness of data within a table where some of the data can be considered as "shared". Sample data and code: -
CREATE TABLE [dbo].[TestSplitOwnership](
[Item] [int] NOT NULL,
[Owner] [char](1) NOT NULL,
CONSTRAINT [PK_TestSplitOwnership] PRIMARY KEY CLUSTERED
(
[Item] ASC,
[Owner] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (1, N'C')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (2, N'C')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (3, N'C')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (4, N'C')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (11, N'1')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (11, N'2')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (11, N'3')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (12, N'1')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (12, N'2')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (13, N'1')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (14, N'2')
GO
INSERT [dbo].[TestSplitOwnership] ([Item], [Owner]) VALUES (15, N'3')
GO
DROP VIEW [dbo].[vwTestSplitOwnership];
GO
CREATE VIEW [dbo].[vwTestSplitOwnership]
WITH SCHEMABINDING
AS
SELECT [TestSplitOwnership].[Item],
[TestSplitOwnership].[Owner]
FROM [dbo].[TestSplitOwnership]
WHERE [Owner] <> 'C'
UNION ALL
SELECT [TestSplitOwnership].[Item],
A.[Owner]
FROM [dbo].[TestSplitOwnership]
CROSS JOIN (
SELECT DISTINCT [Owner]
FROM [dbo].[TestSplitOwnership]
WHERE [Owner] <> 'C'
) AS A
WHERE [TestSplitOwnership].[Owner] = 'C';
GO
CREATE UNIQUE CLUSTERED INDEX [PK_TestSplitOwnership]
ON [dbo].[vwTestSplitOwnership] (
[Item] ASC,
[Owner] ASC
)
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY];
GO
The intent is "C" is not an actual owner - that all the records with "C" as their owner are effectively shared for all other owners, and this we need to ensure uniqueness of ownership based on the query in the view, e.g., any "owned" items can not be the same as a "shared" item. Issue is that I cannot put a PK on the view as it contains the UNION.I cannot change the data structure, but any suggestions as to how I could implement such a constraint would be welcome.
June 24, 2019 at 9:34 am
As you discovered, you can't index a View that has a UNION in it. In truth, if you need this type of design it does indicate a design flaw; if you need to maintain uniqueness across 2 tables.
You could try to enforce this with a trigger, but you'll need to be very careful with this. You will need to make sure that you have cause the appropriate locking on both tables (honestly not something I am very familiar with). This is to avoid things like a race condition allowing 2 separate INSERT statements putting the same value in the different tables. This will likely mean you'll want an INSTEAD OF trigger as well.
Something like that will come at a performance cost as well, and locking both tables when you're only inserting into one of them really doesn't seem like an ideal solution.
I know you said you can't, but really I would look at trying to change the design of your database, if this is really what you need implemented.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 24, 2019 at 1:11 pm
Just use a custom function as a constraint:
CREATE FUNCTION dbo.chkTestSplitOwnership (@Item int, @Owner char(1))
RETURNS int
AS
BEGIN
DECLARE @retval int=0
SELECT @retval =1
WHERE NOT EXISTS(SELECT *
FROM [dbo].[TestSplitOwnership] o
WHERE o.Item = @Item
AND o.Owner <> @Owner
AND @Owner = 'C')
AND NOT EXISTS(SELECT *
FROM [dbo].[TestSplitOwnership] o
WHERE o.Item = @Item
AND o.Owner = 'C'
AND @Owner <> 'C')
RETURN @retval
END;
Then add a constraint to the table using that function:
ALTER TABLE [dbo].[TestSplitOwnership]
ADD CONSTRAINT chkTestSplitOwnership_Shared_Owned
CHECK (dbo.chkTestSplitOwnership(Item, Owner) = 1);
June 24, 2019 at 2:07 pm
As you discovered, you can't index a View that has a
UNIONin it. In truth, if you need this type of design it does indicate a design flaw; if you need to maintain uniqueness across 2 tables. You could try to enforce this with a trigger, but you'll need to be very careful with this. You will need to make sure that you have cause the appropriate locking on both tables (honestly not something I am very familiar with). This is to avoid things like a race condition allowing 2 separateINSERTstatements putting the same value in the different tables. This will likely mean you'll want anINSTEAD OFtrigger as well. Something like that will come at a performance cost as well, and locking both tables when you're only inserting into one of them really doesn't seem like an ideal solution. I know you said you can't, but really I would look at trying to change the design of your database, if this is really what you need implemented.
They are not in separate tables, they are in the same table already!
June 25, 2019 at 7:50 am
Thanks, implemented and works fine.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply