Primary key and unique indexes question

Question

Post reply

Primary key and unique indexes question

MaxJam

Valued Member

Points: 58
More actions
November 16, 2015 at 4:52 am

#399329

Hi all,
If I have 1 table like this currently:
CREATE TABLE [dbo].[tbl_MyTable](
[VhID] [int] NOT NULL,
[CreatedUTC] [datetime2](3) NOT NULL,
[ExtID] [varchar](50) NOT NULL,
[ORS] [tinyint] NOT NULL,
[PrID] [int] NOT NULL,
CONSTRAINT [PK_tbl_MyTable] PRIMARY KEY CLUSTERED
(
[VhID] ASC
)
) ON [PRIMARY]
And now there are two new UNIQUENESS requirements that state that:
1) We can't have duplicates in VhId & ExtId combined
2) We can't have duplicate in ExtId & PrId combined
but VhID and PrID can be the same in one row so long as ExtID is different
heres an example of case which SHOULD be possible:
VhID| ExtID| PrID
1| abc| 2
1| cde| 2
2| cde| 3
here are examples of cases that should *NOT* be possible :
This first example violates rule 1)
VhID| ExtID| PrID
1| abc| 2
1| abc| 3
Second example violates both rules
VhID| ExtID| PrID
1| abc| 2
1| abc| 2
Third example violates rule 2)
VhID| ExtID| PrID
1| abc| 2
2| abc| 2
Whats the best way to approach this?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

(1) and (2) – create 2 unique indexes.

(3) needs a little more explanation. Are you saying that it's OK for

VhID ==PrID

and

PrID != ExtID

but only on a single row?

If so, what is the check for all of the other rows?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

The first and second examples would violate the unique constraint you already have. Are you looking to replace your existing unique constraint on vhid, or implement the new rules alongside?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Create a unique index on VhID and ExtID and on ExtID and PrID. You might consider converting one of them to the primary key. Remember your primary key doesn't have to be clustered - you'll want to choose your clustered index carefully, since it may not be a good idea to have a wide columns such as ExtID as part of it, from a performance point of view.

John

MaxJam Valued Member Points: 58 More actions · Answer 4

ChrisM@Work (11/16/2015)
The first and second examples would violate the unique constraint you already have. Are you looking to replace your existing unique constraint on vhid, or implement the new rules alongside?

Looking to replace the Primary Key thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

doesn't a modificiation featuring these three constraints satisfy the requirement?

i read "but VhID and PrID can be the same in one row so long as ExtID is different" as unique across three columns

IF OBJECT_ID('[dbo].[tbl_MyTable]') IS NOT NULL

DROP TABLE [dbo].[tbl_MyTable]

GO

CREATE TABLE [dbo].[tbl_MyTable] (

[VhID] INT NOT NULL,

[CreatedUTC] DATETIME2 NOT NULL,

[ExtID] VARCHAR(50) NOT NULL,

[ORS] TINYINT NOT NULL,

[PrID] INT NOT NULL,

CONSTRAINT [UQ_tbl_MyTable_VhID_PrID_ExtID] PRIMARY KEY CLUSTERED ([VhID] asc, [PrID] asc, [ExtID] asc),

CONSTRAINT [UQ_tbl_MyTable_ExtID_PrID] UNIQUE NONCLUSTERED ([ExtID] asc, [PrID] asc),

CONSTRAINT [UQ_tbl_MyTable_VhID_ExtID] UNIQUE NONCLUSTERED ([VhID] asc, [ExtID] asc))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 246965 More actions · Answer 6

This bit

but VhID and PrID can be the same in one row so long as ExtID is different

Needs to be elucidated.

I read it as

There can be only one row where VhID = PrID, as long as ExtID is not equal to them.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MaxJam Valued Member Points: 58 More actions · Answer 7

Thanks all for your help, I have created a clustered primary key on the three columns and added 2 unique indexes on the 2 pairs and tested it.

we are reducing the size of ExtID significantly and also have checked with DBAs and they dont believe that we will hit the limit on the clustered index.

thanks all for your help 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

MaxJam (11/16/2015)
Thanks all for your help, I have created a clustered primary key on the three columns and added 2 unique indexes on the 2 pairs and tested it.
...

This is one option available to you. Don't forget that

a) the PK doesn't have to be clustered

b) clustered indexes don't have to be unique.

c) each row of an ordinary index includes the clustered index key of the row it relates to.

You might get better mileage if you declare one of your two unique indexes as a unique clustered index, instead of the three-key jobby.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden