Table Constraint Problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Table Constraint Problem

Author

Topic

AK
Starting Member

27 Posts

Posted - 2002-03-18 : 05:52:05

I have created 3 tables with constraints:

table 1
item_type (p key)

table 2
item_code (p key)
item_type

table 3
pack_code (p key)
item_type (p key)
item_code

Although there is an item type associated with each item code I want to restrict pack_code to one of each item_type - hence the key on pack_code and item_type in table 3.

Cascades do not work due to this replication. I can get around this by using triggers but is this the best design?

Thanks in anticipation of the usual quality response

Nazim
A custom title

1408 Posts

Posted - 2002-03-19 : 00:33:40

your question is confusing , can you elaborate it.

Esp the use of pack_code (what exactly is this column for and why do you need it).

now when you have a design like this , you are trying to imply that
with different combination of pack_code and item_type you can have a same item_code. is that so.

eg:
Pack_code item_type item_code
A INV AAA
B INV AAA

table 3
----
pack_code (p key)
item_type (p key)
item_code

Am not to sure about ur requirements , but dont feel much comfortable with your design.

quote:

Although there is an item type associated with each item code I want to restrict pack_code to one of each item_type - hence the key on pack_code and item_type in table 3.

--------------------------------------------------------------

AK
Starting Member

27 Posts

Posted - 2002-03-19 : 04:49:15

Thanks

Yes - here is an example

table 1
kitchen
bathroom

table 2
oven, kitchen
fridge, kitchen
bath, bathroom

table 3
blah, kitchen, oven
blah, bathroom, bath

you could not have another row (blah, kitchen, fridge) because I want to restrict pack_code to one item per item_type.

does this make sense?

Nazim
A custom title

1408 Posts

Posted - 2002-03-19 : 07:59:57

Am Sorry it doesnt helps much.

in the context what you are looking for your design might be good enough. but until you dont clearly state your requirements its hard to figure out about your design. start with telling why 3 different tables ?

table 3
blah, kitchen, oven
blah, bathroom, bath
can you have another row

blah kitchen bath??????

--------------------------------------------------------------

AK
Starting Member

27 Posts

Posted - 2002-03-19 : 10:09:17

quote:

why 3 different tables ? :

An item_code is assigned an item type - one item type can be assigned to many item_codes but an item_code can only have one item_type (you could not have (bath kitchen) as bath has already been assigned an item_type of bathroom).

Item_codes are assigned to a Pack_code - many item_codes can be assigned to one pack_code and an item_code may be assigned to more than one pack_code.

I want to restrict each pack_code to one item_code per item_type.

--------------------------------------------------------------

Nazim
A custom title

1408 Posts

Posted - 2002-03-20 : 03:51:25

If i where you , i would have tried something like this.

table 3
------
pack_code
description

table 4
----
pack_code
item_code

HTH

--------------------------------------------------------------

AK
Starting Member

27 Posts

Posted - 2002-03-20 : 05:12:07

Sorry - I should have said - there is another table which defines the pack codes as you suggest.

The question is 'is it possible to enforce the unique pack_code+item_type relationship without running into cascade problems?'

Edited by - AK on 03/20/2002 05:15:52

AK
Starting Member

27 Posts

Posted - 2002-03-20 : 05:49:42

I have a better example:

table 1 = football position (Soccer to our friends in the US i.e. the most popular game in the world (including countries outside the US and also those overseas))
left back
right back
goalkeeper

table 2 = player
david seaman, goalkeeper
fabien barthez, goalkeeper
nigel martin, goalkeeper
gary neville, right back

table 3 = team
manchester united, goalkeeper, fabien barthez
manchester united, right back, gary neville

i.e. I only want one player in each position.

Nazim
A custom title

1408 Posts

Posted - 2002-03-20 : 07:43:42

Coming to your example.

table 1 = football position (Soccer to our friends in the US i.e. the most popular game in the world (including countries outside the US and also those overseas))
left back
right back
goalkeeper

table 2 = player
david seaman, goalkeeper
fabien barthez, goalkeeper
nigel martin, goalkeeper
gary neville, right back

table 3 = team
manchester united, goalkeeper, fabien barthez
manchester united, right back, gary neville

it table 3 i dont think you need to store the type of player. coz i can get that information by linking to table2

eg:
fabien barthez will always be a goal keeper (from table 2)
like wise gary neville will always be a right back.

have a type of player or item_type in third table leads to data redundancy which can lead to data inconsistency.

check with taking some sample data and updating it like changing the player position's.

About cascade delete's , i would be better off writing a trigger then depending on cascade's. i have seen couple of posts here complaining about its working.

quote:

Item_codes are assigned to a Pack_code - many item_codes can be assigned to one pack_code and an item_code may be assigned to more than one pack_code.

I want to restrict each pack_code to one item_code per item_type

--------------------------------------------------------------

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-20 : 08:12:05

I think the idea behind including the player type (position) in table 3 (team) is so that you can enforce a primary key on the fields team + player type, resulting in only one player per position.

This is the essence of this problem - creating a constraint that allows only one player per position.

You could validate the data before the data is inserted but surely there is some relational design (incorporating constraints) that fulfils these business requirements.

quote:
If i where you , i would have tried something like this.

table 3
------
pack_code
description

table 4
----
pack_code
item_code

Nazim, your suggestion above leads to the most logical design for this problem - ie. a table for packs (teams), a table for objects (players) and a "linking" table that resolves the many-to-many link. It does not resolve, however, the need for a constraint saying each pack may have only one item of each type.

I work with AK and we have been chewing over this for ages. Any more thoughts would be very helpful. Otherwise we may have to rely on validating data before it is inserted - not ideal!

============
The Dabbler!

Nazim
A custom title

1408 Posts

Posted - 2002-03-20 : 08:39:46

How about having a composite primary key with pack_code and item_code.

table 4
----
pack_code(pk)
item_code(pk

--------------------------------------------------------------

AK
Starting Member

27 Posts

Posted - 2002-03-20 : 08:42:36

That would not prevent you from being able to select more than one item code per item type

e.g.

Manchester united, david seaman
Manchester united, fabien barthez

Nazim
A custom title

1408 Posts

Posted - 2002-03-20 : 08:46:23

its a composite key AK. so , it would be a problem adding the records the way you want.

e.g.

Manchester united, david seaman
Manchester united, fabien barthez

this wouldnt be a problem.

only problem will be if you try to add another same combination which will not be allowed and i think that is what you are looking for.

--------------------------------------------------------------

AK
Starting Member

27 Posts

Posted - 2002-03-20 : 08:53:48

No - this is not what I'm looking for.

david seaman and fabien barthez are both goalkeepers - I want a maximum of one goalkeeper per team.

Nazim
A custom title

1408 Posts

Posted - 2002-03-20 : 09:00:54

ok, then i think this design too wouldnt do much. supposing if a player changes his position?.

you have to modify that at different places.

i would stick with this design and write triggers to take care of the validation's .

table 3
pack_code (p key)
item_code (p key)

--------------------------------------------------------------

Edited by - Nazim on 03/20/2002 09:04:32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources