Can you explain the difference between Technical and Functional integrity?

Technical Integrity

This is also named Referential integrity,

Given a Parent-Child relation the keyword here is the Parent table!

This means that after subsetting a check is done if all children records have a corresponding Parent record!

Subset performs a check after subsetting if all child records have a corresponding parent record.

If there are missing records these parent records are added

Only if the situation is consistent all constraints will be successfully restored.

SITUATION 1: Referential Integrity is NOT ok!

Missing is Parent Key 3 where CHILD_KEY 3 is referring to.

After subsetting Subset detects the missing record and adds this to the Parent Table

SITUATION 2: Referential Integrity is ok!

Functional Integrity

Functional Integrity is the other way around.

Given a Parent-Child relation the keyword here is the Child table!

First thing to bare in mind is that Functional Integrity really is functional. Technically the integrity is alright.

So, what ís it?

Functional Integrity means that you want all CHILD records belonging to a PARENT record.

Huh? But what is the use? We are subsetting, so why should I want all records.?

True, but....

In some situations you require not to have a subset but , due to calculations or checks, require all records.

Imagine you have:

• a PARENT_TABLE with all Countries

• a CHILD_TABLE With alle Presidents

• a SUM_TABLE containing the total number of presidents per country.

The key is the SUM_TABLE.

Assume you want (FUNCTIONAL specification!) that the number in this table actually reflects to the number of presidents in the CHILD_TABLE.

SITUATION 1: Referential Integrity is ok but Functional Integrity is not.

In this example you can see that PARENT_TABLE and CHILD_TABLE have a correct Technical Integrity. No problem.

But The SUM_TABLE contains the number of Presidents for that country. And we have decided that this number must match the actual presidents in the CHILD_TABLE.

SUM_TABLE says that France has 2, Germany has 3 and The USA has 2 presidents.

In the CHILD_TABLE we only see for France 2, Germany 2 for the USA 1 president.

In other words, if we do a reporting check we see that the addition of presidents in CHILD_TABLE does not match SUM_TABLE.

If the functional requirement is to have SUM_TABLE reflect the records in the CHILD_TABLE you should require ALL child records of PARENT_TABLE.

After Subsetting AND you have specified to use Functional Consistency all the CHILD_RECORDS are added. Once this is done the SUM_TABLE matches the number of records in CHILD_TABLE.

Below the remaining presidents are added.

SITUATION 1: Referential Integrity is ok and Functional Integrity as well.