July 5, 2009 at 8:55 am
Hi all,
I have 2 tables Category and Product. Table Product has composite primary key of 2 columns ProductID and CategoryID, so ProductID is not unique because it can be product whith ID=1 in category 1 and 2. My problem is that when I add row to product table I get a new auto increment ID that does not check categoryID.
This code shows the problem:
DROP TABLE Product
DROP TABLE Category
CREATE TABLE Category
(
[CategoryID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [varchar](200) NOT NULL
)
CREATE TABLE Product
(
ProductID [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NOT NULL,
[Name] [varchar](200) NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC,
[CategoryID] ASC
)
)
INSERT INTO Category ([Name])
VALUES ('Category1')
DECLARE @CategoryID1 int
SET @CategoryID1 = @@IDENTITY
INSERT INTO Category ([Name])
VALUES ('Category2')
DECLARE @CategoryID2 int
SET @CategoryID2 = @@IDENTITY
INSERT INTO Product ([CategoryID], [Name])
VALUES (@CategoryID1, 'Product 1 for Category 1')
INSERT INTO Product ([CategoryID], [Name])
VALUES (@CategoryID1, 'Product 2 for Category 1')
INSERT INTO Product ([CategoryID], [Name])
VALUES (@CategoryID2, 'Product 1 for Category 2')
INSERT INTO Product ([CategoryID], [Name])
VALUES (@CategoryID2, 'Product 2 for Category 2')
SELECT * FROM Product
Result is:
Product ID | CategoryID | Name
------------------------------------------------------------
1 | 1 | Product 1 for Category 1
2 | 1 | Product 2 for Category 1
3 | 2 | Product 1 for Category 2
4 | 2 | Product 2 for Category 2
But I need:
Product ID | CategoryID | Name
------------------------------------------------------------
1 | 1 | Product 1 for Category 1
2 | 1 | Product 2 for Category 1
1 | 2 | Product 1 for Category 2
2 | 2 | Product 2 for Category 2
Is it posible? 🙂
July 5, 2009 at 9:26 am
paulneyman (7/5/2009)Is it posible? 🙂
Not with an identity column. If you really need this, you may need to do it in a trigger.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 5, 2009 at 9:31 am
Ok, can you write a sample how to do that?
July 5, 2009 at 10:05 am
Hi,
another way to fix this problem would be to normalize your table structure.
Otherwise you could run into duplicates:
If someone would add two identical products in two different categories, how would you know?
I would create a table with product information only, a second table with category information only and a third table to store product id with corresponding category id.
To insert a single new product I would use something along those lines:
DECLARE @t TABLE (id int)
-- insert new product into product table
INSERT INTO Product(col1 , col2)
OUTPUT INSERTED.ProductID -- store the internal product id
INTO @t
VALUES(@a,@b)
-- insert new product into ProductCategory table together with the original category id
INSERT INTO ProductCategory(prodID,catID)
SELECT TOP 1 id,@c FROM @t
This code snippet is just a brief description to show the basic concept...
July 5, 2009 at 10:17 am
I see your point, but the code I wrote is not a real DB structure.
You solution does not suit.
July 5, 2009 at 10:50 am
Why do you want the dependancy of both pk columns?
- an identity column auto increments its value by the increment value. If not tampered with that definition, it will generate unique values. (best is to support it with a unique index).
Do you want a product with No 14 that can be both type x an type y ?
If no, why this composite pk ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 5, 2009 at 11:24 am
paulneyman (7/5/2009)
I see your point, but the code I wrote is not a real DB structure.You solution does not suit.
Would it be possible for you to show us the real DB structure?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2009 at 11:29 am
paulneyman (7/5/2009)
I see your point, but the code I wrote is not a real DB structure.You solution does not suit.
Please correct me if the following assumption is wrong:
It looks like you're using the database to store the values in a object oriented structure because the application you're working on is based on a object oriented language.
Not having a "real DB" structure but relying on "real DB" functions like identity columns may cause trouble sooner or later...
July 5, 2009 at 12:39 pm
1) About structure of real DB.
Don't think it's a good idea. The sample I've added fully describe the issue that requires solution.
2) About guess of object oriented structure
I do have an object oriented application. But it I does not matter. I described an issue and I need to solve it. I can not reorganize DB structure.
Guys, lets focus on permanent question. As I've understand it can be solved only using trigger... so can someone show how to do this. Or if there is any other way to do this, please be free to share.
July 5, 2009 at 1:18 pm
paulneyman (7/5/2009)
I can not reorganize DB structure.
Why not?
As I've understand it can be solved only using trigger... so can someone show how to do this. Or if there is any other way to do this, please be free to share.
You need either an INSTEAD OF trigger or a stored procedure through which all inserts into this table are done. Then, instead of just inserting the row you need to find the max value of that ID column for the specific category, add one and use that in the insert.
Be careful. Most methods of doing this either can result in duplicate values under concurrent usage or scale very badly under concurrent usage. That's why we're suggesting getting the DB design into a normalised structure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2009 at 1:28 pm
paulneyman (7/5/2009)
Ok, can you write a sample how to do that?
No... we're not here to do your work for you. We will, however, guide you to find the solution.
As several have noted, your db design is flawed and needs to be redesigned.
If you insist on using a trigger, you've already been told what needs to be done inside it. (Except that you will also need to remove the identity function from the column.)
I would start by reading Books Online (BOL), the SQL Server Help System. You can access it by pressing the {F1} function key while in SQL Server Management Studio (SSMS). Look for "CREATE TRIGGER".
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 5, 2009 at 2:00 pm
WayneS (7/5/2009)
paulneyman (7/5/2009)
Ok, can you write a sample how to do that?No... we're not here to do your work for you. We will, however, guide you to find the solution.
As several have noted, your db design is flawed and needs to be redesigned.
If you insist on using a trigger, you've already been told what needs to be done inside it. (Except that you will also need to remove the identity function from the column.)
I would start by reading Books Online (BOL), the SQL Server Help System. You can access it by pressing the {F1} function key while in SQL Server Management Studio (SSMS). Look for "CREATE TRIGGER".
I concur, your db design is flawed, and fails the 3NF.
The data is obviously not dependent on the key, the whole, and nothing but the key, so help you Codd.
You should be able to redesign the underlying db structure and hide that enhanced structure behind stored procedures/views if necessary.
July 6, 2009 at 1:34 am
So there is a solution based on trigger INSTEAD OF INSERT:
DROP TABLE Product
GO
CREATE TABLE Product
(
[ProductID] int,
[CategoryID] int,
[Name] varchar(MAX) NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC,
[ProductID] ASC
)
)
GO
CREATE TRIGGER InsteadTrigger on Product
INSTEAD OF INSERT
AS
BEGIN
BEGIN TRANSACTION
DECLARE @pk INT
SELECT @pk = ISNULL(MAX([ProductID]),0) + 1
FROM Product
WHERE [CategoryID] = (SELECT [CategoryID] FROM inserted)
INSERT INTO Product ([ProductID],[CategoryID],[Name])
SELECT @pk,[CategoryID],[Name]
FROM inserted
COMMIT TRANSACTION
END
GO
INSERT INTO Product([CategoryID],[Name])
VALUES(1,'Prod 1 for Cat 1')
INSERT INTO Product([CategoryID],[Name])
VALUES(1,'Prod 2 for Cat 1')
INSERT INTO Product([CategoryID],[Name])
VALUES(2,'Prod 1 for Cat 2')
INSERT INTO Product([CategoryID],[Name])
VALUES(2,'Prod 2 for Cat 2')
SELECT * FROM Product
Is it ok? It works :-), but is it good enought?
July 6, 2009 at 1:48 am
There's a chance that will cause duplicate key errors under heavy load. Two queries run at the same time, both do the SELECT MAX and get the same value (shared locks are shared), both try to insert the same value into the table and one fails with a duplicate key error (or maybe even a deadlock)
To fix that, your select max needs a more restrictive lock. Updlock is the one usually used. That however is going to hurt concurrency.
There's no real good way of doing this. Either you risk occasional failures or you risk contention and blocking at higher volumes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2009 at 1:50 am
Lynn Pettis (7/5/2009)
I concur, your db design is flawed, and fails the 3NF.
It's violating BCNF if I'm not mistaken. There's no inter-data dependency (so passed 3NF) but there is a dependency between two of the columns in the key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply