August 21, 2018 at 12:17 pm
I have an interesting problem. An application allows users to "Tag" events with a label. Each user can put a single tag on an event. There is a list of default tags that users can choose from, but users can also define their own "custom" tags. The tags must be unique, and we don't want the users to create a custom tag that matches one of the default tags. Here is the current schema
CREATE TABLE CustomTag
( UserCode INT NOT NULL
,TagName VARCHAR(50) NOT NULL
,PRIMARY KEY ( UserCode, TagName )
);
CREATE TABLE StockTag
( TagName VARCHAR(50) PRIMARY KEY );
Here is the constraint I would like to enforce:
CREATE VIEW AllTags
WITH SCHEMABINDING
AS
SELECT DISTINCT TagName
FROM CustomTag
UNION ALL
SELECT TagName
FROM StockTag;
GO
CREATE UNIQUE CLUSTERED INDEX UX_AllTags
ON AllTags ( TagName );
GO
Of course, this is not allowed. You can't use UNION in an indexed view, nor can you use the DISTINCT clause. Is there any way to enforce this constraint without using a Trigger or a Check constraint that references a Scalar UDF?
Thank you for your help. 🙂
August 21, 2018 at 12:37 pm
One way would be to change your table design to use just one table called Tags, and have a column that indicates TagType (Custom or Stock - perhaps simply C or S), and NOT use that as part of the unique key. That solves the unique problem. As long as you insist on two different tables, you have no alternative to a trigger on both tables.
EDIT: realized right after I posted that you would not want to use the TagType in the uniqueness of the table. Oops!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2018 at 12:37 pm
David Moutray - Tuesday, August 21, 2018 12:17 PMI have an interesting problem. An application allows users to "Tag" events with a label. Each user can put a single tag on an event. There is a list of default tags that users can choose from, but users can also define their own "custom" tags. The tags must be unique, and we don't want the users to create a custom tag that matches one of the default tags. Here is the current schemaCREATE TABLE CustomTag
( UserCode INT NOT NULL
,TagName VARCHAR(50) NOT NULL
,PRIMARY KEY ( UserCode, TagName )
);CREATE TABLE StockTag
( TagName VARCHAR(50) PRIMARY KEY );Here is the constraint I would like to enforce:
CREATE VIEW AllTags
WITH SCHEMABINDING
AS
SELECT DISTINCT TagName
FROM CustomTag
UNION ALL
SELECT TagName
FROM StockTag;
GOCREATE UNIQUE CLUSTERED INDEX UX_AllTags
ON AllTags ( TagName );
GOOf course, this is not allowed. You can't use UNION in an indexed view, nor can you use the DISTINCT clause. Is there any way to enforce this constraint without using a Trigger or a Check constraint that references a Scalar UDF?
Thank you for your help. 🙂
Are custom tags and stock tags really two different entities? Seems like it may be one entity with a property of custom or stock/default. In that case, there wouldn't be a problem. I just don't know why there is the UserID in one table and how that is used since it doesn't sound like the tags are unique by user and tag name and your only looking for tag names that are unique (as opposed to tag name and userid).
Sue
August 21, 2018 at 12:37 pm
Looks to me that your choices are exactly those you have said you don't want: a trigger or a scalar function in a constraint.
August 21, 2018 at 12:43 pm
Why did i notice the "not a trigger" after writing this... >_< At least Lynn addresses the problem at hand above (in that the OP has ruled out all 3 options that would fix this; using 1 table, a Scalar Function in a Constraint, or a Trigger). 🙂 Personally, however, I would prefer a trigger over a Scalar function, but 1 table would be my overall preferred option.
Rather than ensuring uniqueness via a CONSTRAINT/INDEX you could use a TRIGGER to check if the value exists in the StockTag table. If it there is a duplication, you can handle it there. One way would be to use ROLLBACK the transaction in the event that one of the tags exists:
The problem with this, however, is that if you INSERT/UPDATE multiple rows, all of the rows will be rolled back if even one is a duplicate. Therefore, as this is a simple table design, you could instead use an INSTEAD OF:
The latter won't produce an error, however, if there are any duplicates, it'll simply "ignore" those rows.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 21, 2018 at 12:52 pm
I think you all are right - I need to change the design. I really did not want a single table, since the tags are just free-form text and they are not shared between users (except for the pre-defined tags). However, I think that is the best option. As usual, weird problems like this are best resolved by a design change.
Thank you all for your input. 😀
August 21, 2018 at 2:26 pm
Whether you use a single Tag table or not, a trigger would still be much easier than a constraint. Why do you not want to even consider a trigger?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 21, 2018 at 7:22 pm
ScottPletcher - Tuesday, August 21, 2018 2:26 PMWhether you use a single Tag table or not, a trigger would still be much easier than a constraint. Why do you not want to even consider a trigger?
I try to avoid triggers for a few reasons:
1. They aren't "foolproof". They are easily disabled and there are circumstances when they won't fire.
2. They can be performance hogs.
3. They add an extra layer of complexity to your code.
Also, in general I try to solve as many problems as I can in the data structure rather than code. Constraints are part of the data structure, while triggers are code. Constraints help define your data - both to other developers and to the Query Optimizer. The Optimizer can use constraints to make assumptions about your data that allow it to more easily choose an optimum execution plan.
Finally, as I mentioned in an earlier post, I've found that whenever you run into weird little problems like this, it is almost always a sign of a design problem. I think of them as "Design Smells". And design problems are a constant source of future bugs. Get the design right and your code can be almost maintenance free. 😀
August 21, 2018 at 9:08 pm
You can solve this problem with a constraint, but it requires the introduction of a helper table to eliminate the need for a union:
-- Helper table
Drop Table If Exists IndexedViewDoubler
Create Table IndexedViewDoubler(value int)
Insert Into IndexedViewDoubler Values (0),(1)
Go
CREATE or ALTER VIEW AllTags
WITH SCHEMABINDING
AS
Select
CT.UserCode,
CT.TagName
From dbo.CustomTag CT
Join dbo.StockTag ST On CT.TagName = ST.TagName
Cross Join dbo.IndexedViewDoubler -- Double up rows if a custom tag matches a stock tag
GO
ON AllTags ( UserCode, TagName );
August 21, 2018 at 9:53 pm
andycadley - Tuesday, August 21, 2018 9:08 PMI tend to agree on triggers, I find they cause as many problems as they solve and have a habit of making reasoning about a piece of code unnecessarily difficult.You can solve this problem with a constraint, but it requires the introduction of a helper table to eliminate the need for a union:
CREATE UNIQUE CLUSTERED INDEX UX_AllTags
-- Helper table
Drop Table If Exists IndexedViewDoubler
Create Table IndexedViewDoubler(value int)
Insert Into IndexedViewDoubler Values (0),(1)
Go
CREATE or ALTER VIEW AllTags
WITH SCHEMABINDING
AS
Select
CT.UserCode,
CT.TagName
From dbo.CustomTag CT
Join dbo.StockTag ST On CT.TagName = ST.TagName
Cross Join dbo.IndexedViewDoubler -- Double up rows if a custom tag matches a stock tag
GO
ON AllTags ( UserCode, TagName );
That will allow users to have their own tags, as long as they don't match a stock tag and will allow different users to use the same tag.
OK! That is very clever! That is exactly what I wanted, but I couldn't figure out how to make it work. I already changed the table design (which is probably for the best), but I will have to keep this in mind for the future.
Thank you! 😀
August 22, 2018 at 6:33 am
It feels to me like this should be enforced by the application or business layer rather than in the database. Probably the simplest way to do it would be to only insert records via a stored procedure and you could get the sproc to carry out the validation logic.
As you say you have modified the schema, could you share your solution so that others with a similar problem can use it as a reference.
August 22, 2018 at 9:22 am
aaron.reese - Wednesday, August 22, 2018 6:33 AMIt feels to me like this should be enforced by the application or business layer rather than in the database. Probably the simplest way to do it would be to only insert records via a stored procedure and you could get the sproc to carry out the validation logic.As you say you have modified the schema, could you share your solution so that others with a similar problem can use it as a reference.
Well, if I had seen Andy Cadley's solution first, I would have used that rather than change the schema. It is exactly what I wanted. I will definitely keep it in mind when I have a No Intersection rule I want to enforce. (He should write it up into a short article. I think it would make a nice "SQL Spackle" sort of article. 🙂 )
That said, what I actually did was to combine the two tag tables into one and add an "isDefault" flag, like so:
CREATE TABLE ActionReminderTag
( TagId INT PRIMARY KEY
,TagName VARCHAR(50) UNIQUE
,isDefault BIT NOT NULL
);
I then added another table to hold the users:
CREATE TABLE UserReminderTag
( UserCode INT NOT NULL
,TagID INT NOT NULL
,PRIMARY KEY ( UserCode, TagID )
);
Regarding your initial point, about enforcing this in code or in the business layer, my response is, "Yes, you should do that as well." I believe that, as much as possible, the data layer should enforce basic business logic and constraints. I have a few reasons for this.
1. Communication: Constraints in the Data Layer are obvious to anyone looking at the data layer. This gives other developers information about the data structure that they might not see in the code.
2. Other applications might use these tables, and the developers might not know (or forget) to add the constraint
3. Business Layer constraints don't prevent your DBA from adding bad data via ad hoc SQL in SSMS. I know they would never do that, but.... 😉
4. As I said in an earlier post, Constraints also communicate with the SQL Query Optimizer - it uses constraints to make assumptions about your data that allow it to eliminate certain possibilities from the query "Solution Space". Remember, the optimizer has to create a query plan that will always return correct data. There might be no overlaps in the data between the StockTag table and the CustomTag table, but that does not guarantee that overlaps could never occur. Constraints do guarantee just that. So, the Optimizer can take that into account when formulating a plan.
5. Finally, as much as possible, your data structure should match your business process. Someone looking at an ERD of your database (or whatever subset applies to a given process), should be able to understand the process completely.
I hope this helps! 😀
August 22, 2018 at 9:33 am
I can't claim to have invented it, I'm fairly sure I picked it up from this site somewhere. It may even be an article already...
August 22, 2018 at 9:38 am
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 22, 2018 at 9:55 am
I'm not sure why you wouldn't want to do this with a check constrint. It's quite easy:DROP TABLE CustomTag
GO
DROP TABLE StockTag
GO
DROP FUNCTION dbo.CheckNotOnStockTag
GO
CREATE TABLE StockTag
( TagName VARCHAR(50) PRIMARY KEY );
--Here is the constraint I would like to enforce:
GO
CREATE TABLE CustomTag
( UserCode INT NOT NULL
,TagName VARCHAR(50) NOT NULL
,PRIMARY KEY ( UserCode, TagName )
);
GO
CREATE FUNCTION dbo.CheckNotOnStockTag(@TagName varchar(50))
RETURNS int AS
BEGIN
RETURN (SELECT COUNT(*) FROM StockTag WHERE TagName=@TagName)
END
GO
ALTER TABLE CustomTag
ADD CONSTRAINT chk_NotOnStckTag
CHECK (dbo.CheckNotOnStockTag(TagName)=0)
GO
INSERT INTO StockTag(TagName) VALUES ('werwer')
INSERT INTO StockTag(TagName) VALUES ('werwer2')
INSERT INTO CustomTag(UserCode, TagName) VALUES (1, 'asdf')
INSERT INTO CustomTag(UserCode, TagName) VALUES (2, 'azxcv')
INSERT INTO CustomTag(UserCode, TagName) VALUES (3, 'werwer') -- Raises error
SELECT * FROM StockTag
SELECT * FROM CustomTag
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply