| Constraint to force existence in another table? [message #21171] |
Wed, 17 July 2002 02:01  |
ChiChiWaWa
Messages: 5
Registered: July 2002
|
Junior Member |
|
|
I hope I'm in the right forum here, but this is what I want to accomplish, but fail to find a resolve for :
Two tables, Content and Category, both have a Primary Key named ID.
A third table joins a Content to a Category by storing corresponding ID's in a row. A Content can be part of several Categories, hence the reason for the existence of this third table.
However, a Content cannot exist without it being linked to a Category. So, there should be a constraint to check the existence of a corresponding record in the third table, when a record is added to the Content-table.
Using a foreign key, I can enforce a check from the joining table to check the ID's existence in the Content-table as well as the Categories-table, but I cannot ad a foreign key on the Content-table referencing the Content_ID in the joining table, because in that latter table it is not a unique key.
I feel like I'm missing the obvious here, so if any of you could help me out it would greately be appreciated!
|
|
|
|
|
|
|
|
| Re: Subquery in check-constraint ??? [message #21190 is a reply to message #21179] |
Wed, 17 July 2002 11:31  |
ChiChiWaWa
Messages: 5
Registered: July 2002
|
Junior Member |
|
|
But for a Foreign Key I need a unique or primary key in the other referenced table. In the current situation, that is impossible.
Basically what I have is an m:n relation between Content and Category. To realise such a relation, a third table has been created that holds the relations, so I can have a 1:m relation between Content and the formentioned table, and a 1:n relation with Category.
However, I need to check that a Content is related to at least one (or more) category.
See the problem?
|
|
|
|
| Re: Subquery in check-constraint ??? [message #21202 is a reply to message #21179] |
Wed, 17 July 2002 22:19 |
ChiChiWaWa
Messages: 5
Registered: July 2002
|
Junior Member |
|
|
If the description doesn't match the original question, I guess I tried to explain it wrong the first time ...
This delete trigger you suggest is fine if you want to prevent a content with multiple catogories to lose all of its categories.
What I need first is a check that a Content has AT LEAST one category linked to it in the intersection table.
|
|
|
|
| Re: Subquery in check-constraint ??? [message #21216 is a reply to message #21179] |
Thu, 18 July 2002 08:59 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The problem with that is that there are times when that is NOT the case. Where and when would you enforce this?
You can only enforce an 'at least' check during a delete (or an update, if allowed) on the intersection table. This validation cannot be tied to the Content table because no cross-reference rows for a Content can be added until the Content actually exists.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
