Question about requiring.....

Question

Question about requiring.....

Peter-218449

Mr or Mrs. 500

Points: 557
More actions
March 8, 2006 at 4:55 am

#91513

Say I have four tables that look like this:

Table1                          Table 2

T1ID    Descrip            T2ID    Descrip
------   ---------          ------   ---------
1          A                      1          foo
2          B                      2          blah
3          C                      3          stuff

Table 3                        Table4

T1ID    T2ID                T2ID
------   ------               -------
                                      1
                                      2

Here's what I want to enforce. If any T1ID exists in Table 3 it must contain all T2ID's from Table 4. This would be valid:

Table 3

T1ID    T2ID
------   ------
1          1
1          2
2          1
2          2

And this would be invalid:

Table 3

T1ID    T2ID
------   ------
1          1
2          1

Personally, I don’t think you can do it and I think it looks a bit suspect in general. Please prove me wrong if you can.

Thanks

Pete

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

I dont think it can be enforced with a constraint - it seem more like business logic. You could try maintaining Tabl 3 through a trigger and poulate T2ID from Table 4.

Peter-218449 Mr or Mrs. 500 Points: 557 More actions · Answer 2

Hi Allen,

Thanks for the response. I agree, I could do this using some type of store proc/trigger or through the business logic.

But you did address what I am trying to figure out. I also can't think of a way to enforce this simply by a relationship or constraint.

If you can think of any way I can, please let me know. Thanks.

Pete

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

Integrity constraints are applied on a single row. Your definition requires checking multiple rows to determine if a single insert is valid.

Enforcing it would require analyzing the full set of potential insert rows (they would need to be inserted as a complete set) to ensure they meet the rules before the insert is allowed. Any table/key/references constraint would check the individual rows... if inserting two rows (such as T2IDs of 1 and 2), the first row (T2ID = 1) would be rejected because no corresponding T2ID=2 row exists, then the second row (T2ID = 2) would be rejected because no corresponding T2ID=1 row exists.

-Eddie

Eddie Wuerch
MCM: SQL

David le Quesne SSCertifiable Points: 5229 More actions · Answer 4

Hi,

If my understanding is correct, Table 3 is effectively a CROSS JOIN or cartesian between Table 4 and selected rows from Table 1. You could do this with a VIEW, without the need for triggers or constraints.

David

If it ain't broke, don't fix it...

Peter-218449 Mr or Mrs. 500 Points: 557 More actions · Answer 5

Thanks for the responses guys. It sounds as though I definitely cannot do this via relationships and constraints.

If I might, I'd like to pose another question while I have your attention.

Say I have an entity with hundreds of attributes. Some of the attributes can be split over several tables but there are still tables with quite a few attributes. In no case do I have a complete entity record, hence a lot of nulls. Most of these attributes are integers or floats so default values wouldn't really work since they could be actual values.

Any thoughts on how to get rid of the nulls?

Thanks again.

Pete