PostgreSQL Query Catalog

Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints.

Find foreign key columns that name contains twice the name of the referenced (primary) table.

Find foreign key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix that describes the nature of relationship with the referenced table or at least referes to the referenced table. These are too generic names.

Find foreign key constraints in case of which some candidate key values cannot be used as foreign key values. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values.

Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular.

Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular.

Find foreign key constraints that cover one column in case of which the name of refererenced/referencing column is in plural and the name of referencing/refererenced column is in singular.

Find foreign key constraints that referenced column is optional.

Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys.

Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency.

Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns while at the same time the referenced table does have the primary key. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency.

Find foreign key constraints that reference to the columns that are covered by a unique index not a unique key.

Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B.

Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value).

Find foreign key constraints where the candidate key columns (belong to a PRIMARY KEY/UNIQUE constraint) and foreign key columns do not have the same data type and field size. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values.

Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table.

Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column.

Find foreign servers that do not have any associated foreign tables.

Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource."

Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning.