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.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2005-01-12 : 18:41:10
|
| Hi,Why isn't there a simple declarative way of saying that you want at least one entity in a join table linking two other entities?e.g. A product can have many categories but it must have at least one.What's the best way to implement this constraint? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-12 : 19:20:58
|
| I'm a little confused by your post. You say "at least one entity in a join table linking two other entities" followed by "can have many categories but it must have a least one". The second piece is accomplished with a simple one-to-many relationship on both sides of the join table with a unique constraint and no NULLs allowed. I don't have any clue what you mean by the first statement though. Can you give some sample data and explain it with data representation?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-12 : 19:49:28
|
Thanks for your reply.Products========productID int pkProductsCategories==================productID int fkcategoryID int fkCategories==========categoryID int pkIf there's a product in the products table then it has to be related to at least one category but it can be related to many. But a category can be empty and thus not be related to any products. quote:
The second piece is accomplished with a simple one-to-many relationship on both sides of the join table with a unique constraint and no NULLs allowed.
Oh yes of course! I just need to do this....Products========productID int pkprimaryCategory int fkProductsCategories==================productID int fkcategoryID int fkCategories==========categoryID int pk...and have 'primaryCategory' reference a row in 'ProductsCategories'. I've been stuck thinking that that 'primaryCategory' would have to reference the Categories table directly as it would do if it was just 1-Many.I think it would be good to put a check constraint on 'Products' which would make sure that 'primaryCategory' and 'productID' had the same value.Am I making sense? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-12 : 19:57:17
|
| I think it would be good to put a check constraint on 'Products' which would make sure that 'primaryCategory' and 'productID' had the same value.Almost. Wouldn't you actually want:Products========productID int pkcategoryID_primary int fk (references Categories.categoryID)ProductsCategories==================productID int fkcategoryID int fkCategories==========categoryID int pkor, am I misunderstanding what you are trying to do? Looks like your mixing up your entity definition here. A primaryCategory shouldn't be a foreign key of another product or ProductCategory combination, especially if the productID of the Product and ProductsCategory productID must be the same.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-12 : 20:01:23
|
| Yes, but the problem with this is that it makes queries more complicated. If I wanted all of a product's categories then one couldn't just go...SELECT * FROM ProductsCategories WHERE productID = 4;I'm looking for a solution where all of a product's categories are in the same table and maybe there's an extra column on the join table which flags one of the categories as primary. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-12 : 20:09:03
|
| Also, you could end up in the situation where you have a sub-category which is also a primary.I just find it strange that you can ensure that an item is associated with *one* item using referential integrity but if you expand it and add a join table and multiple associations then there's no built in way of making sure there's at least one association. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-12 : 22:09:20
|
| Go look at David's blog:http://weblogs.sqlteam.com/davidm/He has written a few articles about "cardinality" and how it can/can't be enforced and managed in SQL databases. It's definitely a weakness in SQL Server. This site also has some discussion:http://www.datamodel.org/index.html |
|
|
|
|
|
|
Many to Many