An analysis of SQL integrity constraints from an entity-relationship model perspective

Abstract

This paper presents an analysis of the integrity constraints defined in the SQL ISO standard disclosure in the light of the entity-relationship model. It points out what features of integrity constraints in SQL support which features of the entity-relationship model by discussing how to map an entity-relationship schema into a SQL schema. In order to organize the analysis, the paper distinguishes three levels of the entity-relationship model. The first level corresponds to the basic model, augmented with simple specializations. The second level considers totality and more complex specializations, and allows deferred and immediate propagation of deletions. Finally, the third level introduces generalization hierarchies and considers some types of inter-relationship constraints. For the first and second levels, the analysis indicates that the more complex features of the SQL referential integrity construct are necessary only when optimization is considered, but one of the features — propagation of nulls —should be slightly enhanced to easy the optimization task. However, for the third level, the analysis shows that, in most cases, the SQL referential integrity construct can efficiently model subsetting, but that the direct use of SQL integrity constraints to account for mutual exclusion may be very expensive.