Filter Queries
- All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
- Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets, it shouldn't be a problem in case of the relatively small amount of data in the system catalog of a database.
#581. Pairs of non-key column name and type pairs that have in different base tables a different default value
INFORMATION_SCHEMA+system catalog base tablesFind non-key base table columns with the same name and type that have different default values. Be consistent. Columns with the same name and type shouldn't probably have different default values in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)
#582. Pairs of non-key column name and type pairs that have in some base tables a default value and some cases not
INFORMATION_SCHEMA+system catalog base tablesFind non-key base table columns with the same name and type that have in some cases a default value and some cases not. Be consistent. Columns with the same name and type should probably either always have a default value in case of different tables or never have a default value in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)
#583. Parameter name contains the routine name
INFORMATION_SCHEMA+system catalog base tablesFind parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.
#584. Parameter name is the same as the name of a used column
INFORMATION_SCHEMA+system catalog base tablesIf the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code.
#585. Parameter name is the same as the name of a used column (ver 2)
INFORMATION_SCHEMA+system catalog base tablesIf the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code.
#586. Parameter name is the same as the routine name
INFORMATION_SCHEMA+system catalog base tablesFind parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.
#587. Paramtetes with an array type, XML, JSON, or JSONB type
INFORMATION_SCHEMA+system catalog base tablesFind parameters of user-defined routines that type is an array type, xml, json, or jsonb type. Make sure that the parameter name matches the type (perhaps should be in plural).
#588. Partial or case insensitive unique indexes
INFORMATION_SCHEMA+system catalog base tablesFind partial or case insensitive unique indexes. These implement uniqueness constraints that are impossible to enforce with the help of SQL's regular UNIQUE constraint.
#589. Password is unique
INFORMATION_SCHEMA+system catalog base tablesFind columns that potentially contains passwords and that participate in a unique constraint or index
#590. Password should not be open text
INFORMATION_SCHEMA+system catalog base tablesFind base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database.
#591. Patterns of the Boolean expressions of simple CHECK constraints
INFORMATION_SCHEMA+system catalog base tablesFind patterns of the Boolean expressions of simple CHECK constraints (involve only one column). Do not solve the same task in different places differently. The same rule could be implemented with CHECK constraints that have different Boolean expressions. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)
#592. Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column
INFORMATION_SCHEMA+system catalog base tablesFind patterns of the names of constraints and indexes. Make sure that the naming is consistent.
#593. Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with two or three columns
INFORMATION_SCHEMA+system catalog base tablesFind patterns of the names of constraints and indexes. Make sure that the naming is consistent.
#594. Patterns of the names of triggers and rules
INFORMATION_SCHEMA+system catalog base tablesFind the patterns of the names of trigger and rule names. Make sure that the naming is consistent.
#595. Perhaps 0 instead of o
INFORMATION_SCHEMA+system catalog base tablesFind the names of database objects where 0 sign is perhaps used instead of o.
#596. Perhaps a CHECK constraint about required personal name components is missing
INFORMATION_SCHEMA+system catalog base tablesFind base tables that have optional columns for recording both given name and surname and do not have a CHECK constraint that requires that at least one of the name components must be registered in case of each person.
#597. Perhaps a CHECK constraint about the combination of truth values is missing
INFORMATION_SCHEMA+system catalog base tablesFind base tables that have at least two columns that have Boolean type and have at least one Boolean column that is not covered by a CHECK constraint involving more than one Boolean column. The Boolean columns possibly mean that we want to record data about states. Often the states depend on each other. For instance, if an order is archived it must be inactive.
#598. Perhaps a CHECK constraint about the order of events is missing
INFORMATION_SCHEMA+system catalog base tablesFind base tables that have at least two columns that have DATE or TIMESTAMP (with or without time zone) type and do not have any associated CHECK constraint that involves two or more of these columns. The columns mean that we want to record data about events or processes, which often have a certain order. Hence, in case of each row of such a table the values in these columns must be in a certain order. For instance, the end of a meeting cannot be earlier than the beginning of the meeting.
#599. Perhaps an existing domain could be used to define the properties of a base table column?
INFORMATION_SCHEMA+system catalog base tablesFind non-foreifgn key base table columns that have not been defined based on a domain but that have the same properties (data type, field size, default value, and pemisson to use NULLs) as some domain. If you define a domain, then you should try to use it in case of multiple columns.
#600. Perhaps an overcomplicated constraint expression that compares the result of a Boolean expression with a Boolean value
INFORMATION_SCHEMA+system catalog base tablesFind table and domain CHECK constraints that compare the result of a Boolean expression with a Boolean value. If you can choose between two logically equivalent Boolean expressions choose the more simple expression.
| # | Name(activate to sort ascending) | Goal(activate to sort ascending) | Type(activate to sort ascending) | Data source(sorted ascending, activate to sort descending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 581 | Pairs of non-key column name and type pairs that have in different base tables a different default value | Find non-key base table columns with the same name and type that have different default values. Be consistent. Columns with the same name and type shouldn't probably have different default values in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 582 | Pairs of non-key column name and type pairs that have in some base tables a default value and some cases not | Find non-key base table columns with the same name and type that have in some cases a default value and some cases not. Be consistent. Columns with the same name and type should probably either always have a default value in case of different tables or never have a default value in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 583 | Parameter name contains the routine name | Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 584 | Parameter name is the same as the name of a used column | If the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 585 | Parameter name is the same as the name of a used column (ver 2) | If the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 586 | Parameter name is the same as the routine name | Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 587 | Paramtetes with an array type, XML, JSON, or JSONB type | Find parameters of user-defined routines that type is an array type, xml, json, or jsonb type. Make sure that the parameter name matches the type (perhaps should be in plural). | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 588 | Partial or case insensitive unique indexes | Find partial or case insensitive unique indexes. These implement uniqueness constraints that are impossible to enforce with the help of SQL's regular UNIQUE constraint. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 589 | Password is unique | Find columns that potentially contains passwords and that participate in a unique constraint or index | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 590 | Password should not be open text | Find base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 591 | Patterns of the Boolean expressions of simple CHECK constraints | Find patterns of the Boolean expressions of simple CHECK constraints (involve only one column). Do not solve the same task in different places differently. The same rule could be implemented with CHECK constraints that have different Boolean expressions. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 592 | Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column | Find patterns of the names of constraints and indexes. Make sure that the naming is consistent. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 593 | Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with two or three columns | Find patterns of the names of constraints and indexes. Make sure that the naming is consistent. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 594 | Patterns of the names of triggers and rules | Find the patterns of the names of trigger and rule names. Make sure that the naming is consistent. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 595 | Perhaps 0 instead of o | Find the names of database objects where 0 sign is perhaps used instead of o. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 596 | Perhaps a CHECK constraint about required personal name components is missing | Find base tables that have optional columns for recording both given name and surname and do not have a CHECK constraint that requires that at least one of the name components must be registered in case of each person. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 597 | Perhaps a CHECK constraint about the combination of truth values is missing | Find base tables that have at least two columns that have Boolean type and have at least one Boolean column that is not covered by a CHECK constraint involving more than one Boolean column. The Boolean columns possibly mean that we want to record data about states. Often the states depend on each other. For instance, if an order is archived it must be inactive. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 598 | Perhaps a CHECK constraint about the order of events is missing | Find base tables that have at least two columns that have DATE or TIMESTAMP (with or without time zone) type and do not have any associated CHECK constraint that involves two or more of these columns. The columns mean that we want to record data about events or processes, which often have a certain order. Hence, in case of each row of such a table the values in these columns must be in a certain order. For instance, the end of a meeting cannot be earlier than the beginning of the meeting. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 599 | Perhaps an existing domain could be used to define the properties of a base table column? | Find non-foreifgn key base table columns that have not been defined based on a domain but that have the same properties (data type, field size, default value, and pemisson to use NULLs) as some domain. If you define a domain, then you should try to use it in case of multiple columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 600 | Perhaps an overcomplicated constraint expression that compares the result of a Boolean expression with a Boolean value | Find table and domain CHECK constraints that compare the result of a Boolean expression with a Boolean value. If you can choose between two logically equivalent Boolean expressions choose the more simple expression. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View |