| Query goal: | Find base tables without any unique constraints and the primary key. In such tables there are no restrictions for recording duplicate rows. Each row represents a true proposition about the real world. It does not make the proposition truer if one presents it more than once. Moreover, duplicate rows increase data size. Without keys the DBMS lacks vital information about data in the database that it can internally use to choose better execution plans and in this way improve performance of database operations. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | High (Few or no false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | Declare the PRIMARY KEY constraint and possibly UNIQUE (or EXCLUDE) constraints. If the table has unique indexes, then replace these with key declarations in order to bring the specification of the data structure to the higher level of abstraction. |
| Data source: | INFORMATION_SCHEMA only |
| SQL query: | Click on query to copy it
SELECT A.table_schema, A.table_name FROM INFORMATION_SCHEMA.tables A INNER JOIN INFORMATION_SCHEMA.schemata B ON A.table_schema=B.schema_name WHERE (A.table_schema, A.table_name) NOT IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.table_constraints WHERE constraint_type IN ('PRIMARY KEY', 'UNIQUE')) AND A.table_type='BASE TABLE' AND (A.table_schema = 'public' OR B.schema_owner<>'postgres') ORDER BY A.table_schema, A.table_name; |
| Collection name | Collection description |
|---|---|
| Find problems about integrity constraints | A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview |
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
| Category name | Category description |
|---|---|
| Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |
| Validity and completeness | Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness). |
| Reference |
|---|
| Rule 4 in: Delplanque, J., Etien, A., Auverlot, O., Mens, T., Anquetil, N., Ducasse, S.: CodeCritics applied to database schema: Challenges and first results. In: 2017 IEEE 24th International Conference on Software Analysis, Evolution and Reengineering (SANER), pp. 432-436. IEEE, (2017). |