TablesMustHavePkOrBeNamedNpk

TablesMustHavePkOrBeNamedNpk is a custom policy check that finds tables that have a Primary Key AND contain NPK in their name.

Before you begin

Scope

Database

changelog

Relational

  • Liquibase 4.29.0+

  • Python 3.10.14+

  • Configure a valid Liquibase Pro license key

  • Create a Check Settings file

  • Ensure the Liquibase Checks extension is installed. In Liquibase 4.31.0+, it is already installed in the /liquibase/internal/lib directory, so no action is needed.

  • If the checks JAR is not installed, download liquibase-checks-<version>.jar and put it in the liquibase/lib directory.

    • Maven users only:

      Add this dependency to your pom.xml

      file: <dependency> <groupId>org.liquibase.ext</groupId> <artifactId>liquibase-checks</artifactId> <version>2.0.0</version> </dependency>

  • Java Development Kit 17+ (available for Open JDK and Oracle JDK)

  • Linux, macOS, or Windows operating system

Procedure

These steps describe how to create the Custom Policy Check. It does not exist by default in Liquibase Pro.

1

Run this command in the CLI:

liquibase checks customize --check-name=SqlUserDefinedPatternCheck
2

Give your check a short name for easy identification

Use up to 64 alpha-numeric characters only.

TablesMustHaveCreateTableOnly
3

Set the Severity to return a code of 0-4 when triggered.

These severity codes allow you to determine if the job moves forward or stops when this check triggers. Learn more here: Use Policy Checks in Automation: Severity and Exit Code options: 'INFO'=0, 'MINOR'=1, 'MAJOR'=2, 'CRITICAL'=3, 'BLOCKER'=4

4

Set 'OPERATOR' (options: STARTS_WITH, ENDS_WITH, CONTAINS, REGEXP, EQUALS) [STARTS_WITH]:

CONTAINS
5

Set the SEARCH_STRING to this valid regular expression:

npk
6

TablesMustHavePkOrBeNamedNpk

TABLE
7

Set 'CASE_SENSITIVE' to true or false depending on how narrow you want your search to be.

In this example, we will set it to false.

8

Enter this command into the CLI:

liquibase checks enable --check-name=ConstraintMustExist
9

Give your check a short name for easier identification

Use up to 64 alpha-numeric characters only.

[ConstraintMustExist1]: TablesMustHavePK

10

Set the Severity to return a code of 0-4 when triggered.

Options: 'INFO'=0, 'MINOR'=1, 'MAJOR'=2, 'CRITICAL'=3, 'BLOCKER'=4

11

Set the CONSTRAINT_OPERATOR

Options: STARTS_WITH, ENDS_WITH, CONTAINS, REGEXP, ALL

In this example, we will use:

[STARTS_WITH]: ALL

12

Enter the required constraint(s), separated by commas

Options: NOT_NULL, UNIQUE, PRIMARYKEY, FOREIGNKEY, DEFAULT

In this example, we will use:

[PRIMARYKEY]: PRIMARYKEY

13

Set 'CASE_SENSITIVE' to true or false depending on how narrow you want your search to be.

In this example, we will set it to true.

14

Set the MESSAGE to match for regular expression <SEARCH_STRING> was detected in Changeset.

All tables must have primary key.

15

Enter this command into the CLI:

liquibase checks copy --check-name=ChainedChecksTemplate
16

Give your check a short name for easier identification.

In this example we will use:
TablesWithoutPKNamingStandard
17

Set the Severity to return a code of 0-4 when triggered.

Options: 'INFO'=0, 'MINOR'=1, 'MAJOR'=2, 'CRITICAL'=3, 'BLOCKER'=4

18

Enter the shortname, logic conditional (using &&, ||, !), and optional (groupings) for your checks.

Example: "(shortname1 && shortname2) || shortname3":

We will enter:

TablesMustHavePK && TablenameContainsNPK

19

Set 'MESSAGE' [A match for regular expression <SEARCH_STRING> was detected in Changeset .]:

Tables without primary keys must have npk in the table name