The VLDB Journal

, Volume 25, Issue 4, pp 571–596

Possible and certain keys for SQL

  • Henning Köhler
  • Uwe Leck
  • Sebastian Link
  • Xiaofang Zhou
Regular Paper

DOI: 10.1007/s00778-016-0430-9

Cite this article as:
Köhler, H., Leck, U., Link, S. et al. The VLDB Journal (2016) 25: 571. doi:10.1007/s00778-016-0430-9

Abstract

Driven by the dominance of the relational model and the requirements of modern applications, we revisit the fundamental notion of a key in relational databases with NULL. In SQL, primary key columns are NOT NULL, and UNIQUE constraints guarantee uniqueness only for tuples without NULL. We investigate the notions of possible and certain keys, which are keys that hold in some or all possible worlds that originate from an SQL table, respectively. Possible keys coincide with UNIQUE, thus providing a semantics for their syntactic definition in the SQL standard. Certain keys extend primary keys to include NULL columns and can uniquely identify entities whenever feasible, while primary keys may not. In addition to basic characterization, axiomatization, discovery, and extremal combinatorics problems, we investigate the existence and construction of Armstrong tables, and describe an indexing scheme for enforcing certain keys. Our experiments show that certain keys with NULLs occur in real-world data, and related computational problems can be solved efficiently. Certain keys are therefore semantically well founded and able to meet Codd’s entity integrity rule while handling high volumes of incomplete data from different formats.

Keywords

Armstrong database Axiomatization Data profiling Discovery Extremal combinatorics Implication problem Index Key Null marker SQL 

Supplementary material

778_2016_430_MOESM1_ESM.pdf (176 kb)
Supplementary material 1 (pdf 175 KB)

Copyright information

© Springer-Verlag Berlin Heidelberg 2016

Authors and Affiliations

  • Henning Köhler
    • 1
  • Uwe Leck
    • 2
  • Sebastian Link
    • 3
  • Xiaofang Zhou
    • 4
    • 5
  1. 1.School of Engineering and Advanced TechnologyMassey UniversityPalmerston NorthNew Zealand
  2. 2.Department of MathematicsThe University of FlensburgFlensburgGermany
  3. 3.Department of Computer ScienceThe University of AucklandAucklandNew Zealand
  4. 4.The University of QueenslandBrisbaneAustralia
  5. 5.Soochow UniversitySuzhouChina