How to create a compostie field even if field/s can be NULL??

Question

How to create a compostie field even if field/s can be NULL??

zeeshanmajeedbutt

SSC Enthusiast

Points: 178
More actions
September 25, 2007 at 8:42 am

#120615

Hi All,
I have a table called #PromoMeasure with following fields and types.
--Table definition
CREATE TABLE #PromoMeasure
(
ID int IDENTITY(1,1),PromotionType nvarchar(100) NOT NULL, AdType nvarchar(100), DisplayType nvarchar(100),WhereCriteria nvarchar(1000)
)
--Insert records
INSERT INTO #PromoMeasure(PromotionType, AdType, DisplayType,WhereCriteria)
VALUES('BOGOF','SHELF TALKER',NULL,
'[NewUnitsContribution%] =-52')
GO
INSERT INTO #PromoMeasure(PromotionType, AdType, DisplayType,WhereCriteria)
VALUES('Price Promotion',NULL,NULL,
'[NewUnitsContribution%] =-48')
GO
Using PromotionType,AdType, and DisplayType fields I want to create a Composite key.
As you can see in above inter records these fields may be NULL. Is there anyway to achieve the same purpose?
I want to restrict the user to not add a combination of these field that already exist. In that combination out of those three fields the AdType and DisplayType fields can be NULL.
How to do that?
Please help.
Thanks a million in advance.
Zee

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

CREATE UNIQUE INDEX IDX_PromoMeasure_PromotionType_AdType_DisplayType ON #PromoMeasure (PromotionType, AdType, DisplayType)

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Just keep in mind: NULL must be unique as well.

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Just remember - you can have a UNIQUE index with nullable fields. It cannot be a primary key however. Primary keys don't allow any nullable fields (even if composite key).

Just trying to make sure you didn't mean PRIMARY KEY by COMPOSITE KEY in the title.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

In other words, the following works...

CREATE TABLE #MyHead

(

ColA INT, ColB INT, ColC INT

)

CREATE UNIQUE INDEX AK_SomeKey ON #MyHead (ColA, ColB, ColC)

INSERT INTO #MyHead (ColA, ColB, ColC)

SELECT 1,Null,Null UNION ALL

SELECT 2,Null,Null UNION ALL

SELECT 2,Null,1 UNION ALL

SELECT 2,1 ,1 UNION ALL

SELECT 2,1 ,Null

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)