Unique constraints - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Unique constraints

Author

Topic

arvidb
Starting Member

7 Posts

Posted - 2011-04-06 : 05:45:41

Dear Friends!

Assume you have the following design:

(A modality consists of a number of parameter sets and each parameter set consists of a number of parameters).

tbl_Modalities
m_id (PK)
m_name

tbl_ParameterSets
ps_id (PK)
m_id
ps_name

tbl_Parameters
p_id (PK)
ps_id
p_name

A modality has a name (m_name) that has to be unique. This is achieved by using a unique index on the m_name column. (OK!)

A modality consists of a number of parameter sets stored in the table tbl_ParameterSets. A parameter set name must be unique within its modality which is achieved by using a unique index on the column pair [m_id, ps_name]. (OK!)

A parameter set consists of a number of parameters stored the tbl_Parameters. A parameters name must be unique not only within the parameter set, but also within the modality. My question to you is how this is achieved in the best way. (HOW?)

Thankful for suggestions!

Sachin.Nand

2937 Posts

Posted - 2011-04-06 : 07:54:26

Just an idea.

Map m_id also to tbl_Parameters and create a combined unique key on p_name and m_id.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-06 : 08:11:44

This smells like homework. Is it?

--
Gail Shaw
SQL Server MVP

arvidb
Starting Member

7 Posts

Posted - 2011-04-06 : 08:50:18

quote:
Originally posted by Sachin.Nand

Just an idea.

Map m_id also to tbl_Parameters and create a combined unique key on p_name and m_id.

PBUH

Thank you for your suggestion!

I am not sure that I know what you mean by map.

My idea is to use a computed column for the m_id (since it depends on the ps_id) in tbl_Parameters and then create a combined unique key on p_name and m_id as you suggested.

arvidb
Starting Member

7 Posts

Posted - 2011-04-06 : 08:55:37

quote:
Originally posted by GilaMonster

This smells like homework. Is it?

--
Gail Shaw
SQL Server MVP

Nope! Just my way to express myself…
(I tried to formulate what I am looking for in a clear way.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources