Create a compostie field evn if field/s can b NULL

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

Transact-SQL (2000)

Create a compostie field evn if field/s can b NULL

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-25 : 11:01:09

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

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-25 : 11:01:37

The following seem to be working.

CREATE UNIQUE NONCLUSTERED INDEX IDX1_YourTable ON dbo.YouTable
(
field1,
field2,
field3
) ON [PRIMARY]
GO

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-25 : 11:13:45

This seem to be working. thanks again.
But what if I also want the field1 not to be NULL in any case?.

Meaning I want to have a composite Key based on field1,field2,field3. I want to allow the user to keep the field2 or field3 as NULL. But I want to restrict the user to not keep field1 as NULL.

Is there anyway to do that?

Thanks a million in advance.

Zee

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-25 : 11:21:12

Can you not define field1 as NOT NULL when you create the table? Or can you not ALTER field1 to be NOT NULL?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 11:21:36

Create the underlying table with

Col1 <data type here> NOT NULL.

E 12°55'05.25"
N 56°04'39.16"

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-25 : 16:56:36