One-Many relationship constraint

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

SQL Server Development (2000)

One-Many relationship constraint

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-03 : 13:11:37

Hi there,

When you have a 1-Many relationship, how can you force the database to ensure that there's always at least one item referencing an item?

For example, if I have a product catalog with a table of categories, how do I make sure that at least one product is referencing a category?

In otherwords, I want a strict one to many relationship and not have any possibility of a 1 to zero instance.

Cheers,

X.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-03 : 13:17:52

I'm assuming you mean the child...

Where you gonna get the data? What the key of the child?

Got any DDL?

What value do you want to use for the child key? Gitta be the parent and....?

What?

Brett

8-)

MuadDBA

628 Posts

Posted - 2004-03-03 : 15:46:29

If you want strict one to many, woulnd't 1 to 1 also be out of the question?

I am curious as to what situation you are thinking about this for....obviously not the product/category example you gave, because a one to zero relationship wouldn't be out of the ordinary there.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-03 : 16:51:45

Thanks for your reply. Here's the full picture..

products
--------
productID int pk

Categories
----------
categoryID int pk

Prod_Cat
--------
productID int fk
categoryID int fk
primaryCat tinyint (yes/no - unique with productID)

So a product can be of more than one category and I want a product to always have a primary category.

There's a query that depends on this..

(SELECT p.productID,prodName,
CASE --look up product's primary category's name if this isn't its primary category
WHEN pC.primaryCat = 0 THEN

(SELECT c.categoryName
FROM products p2
INNER JOIN Prod_Cat pC ON p2.productID = pC.productID and pC.primaryCat > 0
INNER JOIN categories c ON pC.categoryID = c.categoryID
WHERE p2.productID = p.productID
)

ELSE null
END as 'catName'
FROM products p
INNER JOIN Prod_Cat pC ON p.productID = pC.productID
WHERE pC.categoryID = @categoryID)

So basically, given a categoryID you're returning all products of that category. But if the given category is not a product's primary category then you also want to return the name of its primary category.

So, there has to be primary cat for each product else there's a possibility of the name being returned as null.

quote:
If you want strict one to many, woulnd't 1 to 1 also be out of the question?

I didn't mean that strict.

X.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-03 : 17:03:09

I have a post on my weblog about impementing a One-To-One.. Changing to One-To-Many shouldn't be that difficult.... It only works for SQL2K..you'll see why..

[url]http://weblogs.sqlteam.com/davidm/archive/2003/11/28/623.aspx[/url]

DavidM

"SQL-3 is an abomination.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-03 : 17:09:52

I knew I saw that somewhere before. Should have known David. Good stuff!!

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-08 : 13:33:29

Hey byrmol,

I'm a bit confused by your example.

If a customer has to be associated with an individual, why not have the customer reference individuals instead of the other way around and just enter the individual first? That way, you'd still not be able to have a customer without a corresponding individual but without the view/trigger complexity.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-08 : 17:46:09

X Factor,

That makes no sense at all.
The example is a One-to-One not a 0-to-One. There MUST be entries in BOTH tables. Which is similar to your request. The view enforces the One-to-One requirement.
In your case, you would create a view with the join of Products and Prod_Categories and then add a INSTEAD OF INSERT trigger that enforces you rule.. New products would then have to satisy the views constraints....

DavidM

"A Human Clone is a result of not implementing a Key Constraint."

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-08 : 18:59:24

OK!

Then I think it would be clearer if it said..

quote:
There MUST be entries in BOTH tables.

instead of

quote:
But the Customer entity MUST have a corresponding row in the Individuals entity.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources