Help on table constraints

[techipa]

Hi All,

I have to create one table and the data stored in the table would be something like this

ID 1 2 3 4 5 6 7 8 9
Step 1 1 1 2 2 3 3 3 3
Role 1 2 3 1 2 1 2 3 4

The ID is always unique. One step can have more multiple Roles.

May I have some suggestions on how to create the table constraints and indexes?

Thanks in advance
-techiPA

 

[Crowley16]

set the ID to be the PK, then you can have a unique index on ID and Step, then another on Step and Role.

I think that would cover it...

--------------------
Procrastinate Now!

 

[AlexCuse]

>>>you can have a unique index on ID and Step, then another on Step and Role.


You actually can't have a unique index on Step:

Step 1 1 1 2 2 3 3 3 3

Ignorance of certain subjects is a great part of wisdom

 

[techipa]

Should it be like this:

CREATE UNIQUE CLUSTERED INDEX StepDef_ind
ON StepDef (ID, Step)

CREATE UNIQUE INDEX StepDef_ind1
ON StepDef_ind (Step, Role)

The Step is FK on this table. And the ID is FK to some other table.

 

[techipa]

Crowley and Alen, I appreciate your reply.

I do not understand the need of having unique index on ID and Step. I definately need unique index on Step and Role.

-techiPA

 

[AlexCuse]

techipa - the first one needs to be nonclustered (only 1 clustered per table) and non-unique.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Crowley16]

I meant (step & role) together would be a unique composite index

so 3 indexes altogether:
ID - PK
(ID & Step) - Composite Unique Index
(Step & Role) - Composite Unique Index

--------------------
Procrastinate Now!

 

[techipa]

Crowley16, the PK shoudl be the clustered or one of the unique index should be the clustered index?

 

[Crowley16]

the PK should be clustered, I'm assuming you're using an identity for this field though.

--------------------
Procrastinate Now!

 

[techipa]

Crowley16, I am not using Identity for the ID field. We will maintain it.

 

[Crowley16]

the most likely candidate for clustering is still the ID but depending on the transactions/reads on the table, it might not be necessary to have a clustered index at all...

--------------------
Procrastinate Now!

 

[gmmastros]

In my opinion, every table should have a clustered index. Most of the time, the primary key is clustered, but it doesn't have to be that way. In some rare cases, clustering another column (or columns) makes the most amount of sense.

When not to cluster the primary key?

When you are joining to this table on column(s) that are not the primary key more often then when you join to this table on the primary key. Nobody can answer this except for you because we can't predict how you will use this table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

Thanks crowley16 and George.

The basics posted by George really helps.
-techiPA

 

[techipa]

Why do I need unique index on ID and Step?

 

[AlexCuse]

You don't, but it may improve speed. As George said, it depends how you're querying the table

Ignorance of certain subjects is a great part of wisdom