Unique Value based on other values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Unique Value based on other values

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-03-30 : 09:25:33

Hi,

I'm not sure if this is possible and I've been Googling to no avail for a while now so I thought I would result to you guys. I expect I'm using the wrong terms but basically:

I have a table (CMRC_Products) each product has a unique Id (ProductId). The database is for an ecommerce store which utilises URL Rewriting so I have a UrlPath field too. Each product can have a number of variations so I have a ProductGroupId, the URL is then built up to produce: /ParentProductName/ChildProduct.

This is fine, my problem however is that on inserting a new value I would like to ensure that UrlPath is unique but only in the same ProductGroupId (null if its not a variation).

Is this possible on a database level or will I have to revert to validation checks only?

Many thanks for your help in advance, I apologise if I'm not clear.

Tim

Products Table SQL:


CREATE TABLE [CMRC_Products] (
	[ProductId] [int] IDENTITY (1, 1) NOT NULL ,
	[ProdName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[UrlPath] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[ProductGroupId] [int] NULL 
	CONSTRAINT [PK_CMRC_Products] PRIMARY KEY  CLUSTERED 
	(
		[ProductId]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO [dbo].[CMRC_Products]([ProdName], [UrlPath], [ProductGroupId]) VALUES ('Parent Product', 'Parent', null)
INSERT INTO [dbo].[CMRC_Products]([ProdName], [UrlPath], [ProductGroupId]) VALUES ('Variation Small', 'Small', 1)
INSERT INTO [dbo].[CMRC_Products]([ProdName], [UrlPath], [ProductGroupId]) VALUES ('Variation Large', 'Large', 1)

Should produce 3 values:

/Parent
/Parent/Small
/Parent/Large

If inserting:
/ADifferentParent/Small (UrlPath = 'Small') should be ok
If inserting:
/Parent/Small it should fail...

jhermiz

3564 Posts

Posted - 2006-03-30 : 10:11:54

I think the term you are looking for is a composite key? Yes this is possible the composite key ensures there are unique dual entities.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-03-30 : 10:28:45

Thanks, that was what I thought I needed.

One question based on that, if I make a composite key which is based on the ProductGroupId and UrlPath, if the ProductGroupId is null will that affect it?

Thanks again,

Tim

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-03-30 : 10:32:13

nm answered my own question, thanks for your help.

Tim

rythm123us
Starting Member

27 Posts

Posted - 2006-03-30 : 11:04:06

Try This
You can create a view then do Inserts on the View, and at the same time create an "INSTEAD OF INSERT" trigger.
But I think using constraints would be a better option, if you can make it work.

****VIEW****
CREATE VIEW [dbo].[vu_CMRC_Products]
AS
SELECT ProductId, ProdName, UrlPath, ProductGroupId
FROM dbo.CMRC_Products

****TRIGGER****
CREATE TRIGGER trg_Instead_Insert_CMRC_Products on vu_CMRC_Products
INSTEAD OF INSERT
AS
DECLARE
@rows int,
@prodName nvarchar(50),
@urlPath nvarchar(50),
@productGroupId int;

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT
@prodName = ProdName,
@urlPath = UrlPath,
@productGroupID = ProductGroupID
FROM inserted

SELECT @rows = count(1)
FROM CMRC_Products
WHERE ProductGroupID = @productGroupID
AND UrlPath = @urlPath

IF @rows = 0
BEGIN
INSERT INTO CMRC_Products
(ProdName, UrlPath, ProductGroupID)
VALUES
(@prodName, @urlPath, @productGroupID)
END

END

****INSERT STATEMENTS****
INSERT INTO [dbo].[vu_CMRC_Products]([ProdName], [UrlPath], [ProductGroupId]) VALUES ('Parent Product', 'Parent', null)
INSERT INTO [dbo].[vu_CMRC_Products]([ProdName], [UrlPath], [ProductGroupId]) VALUES ('Variation Small', 'Small', 1)
INSERT INTO [dbo].[vu_CMRC_Products]([ProdName], [UrlPath], [ProductGroupId]) VALUES ('Variation Large', 'Large', 1)
INSERT INTO [dbo].[vu_CMRC_Products]([ProdName], [UrlPath], [ProductGroupId]) VALUES ('Variation Small', 'Small', 1)

Only 3 rows will be inserted. The 4th one is a duplicate and will be ignored.

Hope this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources