Geek Answers Handbook

Best way to ensure data integrity constraint?

I have 3 tables (you can call them Foo, Bar and Baz.

Tables:

Foo

  • Fooid

Bar

  • Barid
  • Fooid

Baz

  • Bazid
  • Barid
  • AnotherValue

Obviously, foreign keys make each Baz associate with a bar and, therefore, is associated with Foo. Now I want to make sure that for each Baz set with the same "AnotherValue", all Foo associated with it are unique

For example, if I had

Foos (1, 2, 3) Bars ((10, 1), (11, 1), (12, 1), (13, 2)) Bazs ((100, 10, "a"), (101, 10, "b"), (102, 13, "a"), (104, 11, "b"))

this should be blocked because both Baz 104 and baz 101 both have AnotherValue "b" and Foo 1.

The options I was thinking about (in the order of my current preferences)

Indexed view

I could create a view on these three tables and put a unique index in two columns

Computed Column

Add FooId as a computed column in Baz. Then add the index to AnotherValue and FooId.

Check restrictions

I am sure this can be added and will work. I have not used validation restrictions, and I'm not sure if this is the best way to do this.

Trigger

It just seems ugly to me.

+4
source share
2 answers

I'm not sure I fully understand the question, but it looks like you want to transfer the FooId to the Baz table and add an alternate key (unique attribute) to FooId, AnotherValue.

 Baz •BazId •BarId •FooId •AnotherValue
+1
source

Just reuse using different names:

 Parent (ParentID) (1, 2) Child (ChildID, ParentID) ((10, 1), (11, 1), (13, 2)) GrandChild (GCID, ChildID, GCAndParentVal) ((100, 10, "a"), (101, 10, "b"), (102, 13, "a"), (104, 11, "b"))

Could you have this field in another table with parentID and baz.anotherValue, and not include it in the table with childID?

like this:

 Parent (ParentID) (1, 2) Child (ChildID, ParentID) ((10, 1), (11, 1), (13, 2)) GrandChild (GCID, ChildID, ...) ((100, 10, ...), (101, 10, ...), (102, 13, ...), (104, 11, ...)) AnotherChildValue (ParentID, AnotherVal) ((1, "a"), (1, "b"), (2, "a"), (1, "b"))
0
source

More articles:


All Articles