Ensure column combinations are Unique

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)

Ensure column combinations are Unique

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2007-02-06 : 07:37:15

I have the following table:

CREATE TABLE [LogonSetMapping] (
	[UniqueKey] [int] IDENTITY (1, 1) NOT NULL ,
	[MachineName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LogonSetNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SiteKey] [int] NULL ,
	[AmcatRemoteAgent] [bit] NULL CONSTRAINT [DF_LogonSetMapping_AmcatRemoteAgent] DEFAULT (0),
	[LastLoggedOnAgentID] [int] NULL ,
	[RemoteAgentKey] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClientTypeID] [int] NULL CONSTRAINT [DF_LogonSetMapping_ClientTypeID] DEFAULT (1),
	CONSTRAINT [PK_LogonSetMapping] PRIMARY KEY  CLUSTERED 
	(
		[UniqueKey]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

I need to set up some form of Rule that will allow only the following:

Unique 'Machine Name' per 'SiteKey'
and
Unique 'Machine Name' per 'LogonSetNumber'
whenever a new set of data is inserted.

Can anyone show me how this would be possible please?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-06 : 07:41:42

Why don't you add UNIQUE constraint on MachineName, LogonSetNumber and SiteKey columns?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

patshaw
Posting Yak Master

177 Posts

Posted - 2007-02-06 : 07:49:27

Individually, each column can be duplicated in the table, but only when the combinations above are non-unique should they be rejected. By adding an individual unique constraint to each column then 'single' duplicate values will not be allowed which is no good in this case.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 07:52:46

As Harsh told you, create the UNIQUE CONSTRAINT over several column!

Peter Larsson
Helsingborg, Sweden

patshaw
Posting Yak Master

177 Posts

Posted - 2007-02-06 : 09:05:14

I see now, I didn't realise you could have a UNIQUE constraint that spans multiple columns. Thanks guys.

rcurrey
Starting Member

30 Posts

Posted - 2007-02-06 : 15:46:50

If I understand you correctly, you will need TWO unique constraints. The first will be for 'Machine Name' per 'SiteKey' and the second for 'Machine Name' per 'LogonSetNumber'.

Hope this helps.

Thanks,
Rich

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources