Query goal: | Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, but have a name that reffers to the possibility that these are used to record references to a user. Exclude columns that have the default value CURRENT_USER or SESSION_USER. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Medium (Medium number of false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Declare the foreign key constraint if it is missing. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH key_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.oid as target_table_oid, unnest(o.conkey) AS target_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype in ('u','p')) t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false), fk_columns AS ( select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.oid as target_table_oid, unnest(o.conkey) AS target_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype = 'f') t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false ), user_defs AS (SELECT c.table_schema, c.table_name, c.column_name FROM information_schema.columns AS c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name) INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name) WHERE c.table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND (column_default IS NOT NULL OR domain_default IS NOT NULL) AND coalesce(c.column_default, domain_default)~*'(current_user|session_user)') SELECT table_schema, table_name, column_name FROM INFORMATION_SCHEMA.columns AS c WHERE column_name~*('^(isikukood|personal_code|.*_by|(id|kood|code)(_){0,1}(isik|tootaja|kasutaja|subjekt|person|worker|user|subject)|(isik|tootaja|kasutaja|subjekt|person|worker|user|subject)(_){0,1}(id|kood|code)|(isik|tootaja|kasutaja|person|worker|user))$') AND (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM key_columns AS pk WHERE pk.table_schema=c.table_schema AND pk.table_name=c.table_name AND pk.column_name=c.column_name) AND NOT EXISTS (SELECT 1 FROM fk_columns AS fk WHERE fk.table_schema=c.table_schema AND fk.table_name=c.table_name AND fk.column_name=c.column_name) AND NOT EXISTS (SELECT 1 FROM user_defs AS ud WHERE ud.table_schema=c.table_schema AND ud.table_name=c.table_name AND ud.column_name=c.column_name) ORDER BY table_schema, table_name, column_name; |
Collection name | Collection description |
---|---|
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 |
---|---|
Relationships between tables | Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly. |
Result quality depends on names | Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results. |
Reference |
---|
This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 5: Keyless Entry. |
Blaha, M.: A retrospective on industrial database reverse engineering projects - part 2. In: Eighth Working Conference on Reverse Engineering, pp. 147–153. IEEE, (2001). https://doi.org/10.1109/WCRE.2001.957818 (Lack of declared foreign keys) |
Blaha, M.R., Premerlani, W.J.: Observed idiosyncracies of relational database designs. In: 2nd Working Conference on Reverse Engineering, pp. 116–125. IEEE, (1995). https://doi.org/10.1109/WCRE.1995.514700 (Informal linkage between tables) |
Weber, J.H., Cleve, A., Meurice, L., Ruiz, F.J.B.:. Managing technical debt in database schemas of critical software. In: Sixth International Workshop on Managing Technical Debt, pp. 43-46. IEEE (2014). |
Al-Barak, M., Bahsoon, R.: Database design debts through examining schema evolution. In: 8th International Workshop on Managing Technical Debt, pp. 17–23. IEEE, (2016). https://doi.org/10.1109/MTD.2016.9 (Lack of declared foreign keys) |
Rule 7 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). |
Smell "Missing constraints": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018). |
Foidl, H., Felderer, M., Biffl, S.: Technical debt in data-intensive software systems. In: 45th Euromicro Conference on Software Engineering and Advanced Applications, pp. 338 –341. IEEE, (2019). https://doi.org/10.1109/SEAA.2019.00058 (Missing (foreign key) constraints) |
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Not using referential integrity constraints) |
Balogh, G., Gergely, T., Beszédes, Á., Szarka, A., Fábián, Z.: Capturing expert knowledge to guide data flow and structure analysis of large corporate databases. Acta Polytechnica Hungarica 16(4), 7–26 (2019). (Isolated table) |
Dintyala, P., Narechania, A., Arulraj, J.: SQLCheck: automated detection and diagnosis of SQL anti-patterns. In: 2020 ACM SIGMOD International Conference on Management of Data, pp. 2331–2345. (2020). https://doi.org/10.1145/3318464.3389754 (No foreign key) |
Mistake (4): https://www.red-gate.com/simple-talk/sql/database-administration/five-simple--database-design-errors-you-should-avoid/ |