A PostgreSQL NOT VALID foreign key skips the initial check of existing rows while enforcing new references. Older unmatched rows remain until repaired and validated. PostgreSQL 17: ALTER TABLE

This guide is for a developer working in Terminal on a Mac who wants to add a foreign key to populated tables and understand the results. The complete node-postgres example uses only synthetic article data and follows one unmatched reference from constraint creation through repair and successful validation.

What happens to existing rows and new writes?

Consider two tables: articles contains article IDs and titles, and article_links contains references to those IDs. A link is unmatched when its non-null article_id has no corresponding article. Adding the foreign key with NOT VALID lets that older mismatch remain while establishing the constraint for subsequent writes. PostgreSQL 17: ALTER TABLE

Operation after adding the constraint Result in this example
Leave an older unmatched link untouched It remains stored
Insert a link referencing an existing article It succeeds
Insert a link referencing a missing article It fails
Change a link’s article ID to another missing ID It fails
Validate while an unmatched link remains Validation fails
Repair all unmatched references, then validate Validation can succeed

These outcomes follow the documented separation between adding and validating the constraint. PostgreSQL 17: ALTER TABLE

An important exception concerns updates that leave the foreign-key value unchanged. In PostgreSQL 18, the implementation can skip the foreign-key check when the old and new keys compare equal. Consequently, changing only an older unmatched link’s label can succeed without repairing its reference. The implementation also contains a special case for a row inserted by the current transaction, so this should not be generalized to every update sequence. PostgreSQL 18 foreign-key implementation

The practical consequence is that successful label edits are poor evidence of completed cleanup. Inspect unmatched references directly, and use validation to establish that the constraint holds across the table.

Which PostgreSQL version and table shape does this require?

The example below uses ordinary, nonpartitioned tables and targets PostgreSQL 17 and 18. Partitioned tables have a significant version boundary: PostgreSQL 17 explicitly disallows declaring foreign keys on partitioned tables as NOT VALID; PostgreSQL 18 introduced that capability. Check the server version rather than inferring support from your installed Node package. PostgreSQL 17: ALTER TABLE, PostgreSQL 18 release notes

For a partitioned referencing table on PostgreSQL 17, the supported direct path is to correct the references and add the foreign key normally, allowing the initial validation scan. If staged validation on the partitioned parent is essential, PostgreSQL 18 supplies the documented capability. This tutorial keeps the table layout simple so that partition hierarchy details do not obscure the row behavior.

The referenced key also needs a suitable uniqueness guarantee. Here, articles.article_id is a primary key. The referencing column deliberately permits NULL: under the default MATCH SIMPLE rule, a null reference does not need a matching article. Add NOT NULL separately if every link must identify an article. PostgreSQL 18: CREATE TABLE

Run a complete synthetic example from your Mac

You need a running PostgreSQL database, an existing working local connection configuration, and a Node.js version supported by node-postgres. In a project directory in Terminal, install the package with the command below, then save the complete program as not-valid-demo.mjs. The official node-postgres introduction documents the package installation. node-postgres documentation

npm install pg

The program uses new pg.Client(), which reads PostgreSQL connection environment variables such as PGHOST, PGPORT, PGDATABASE, and PGUSER. Use the settings that already connect to your local database. node-postgres: Connecting

Both tables are temporary and exist only for this connection’s session. PostgreSQL requires temporary-table foreign keys to reference temporary tables, so the program creates both sides together. PostgreSQL 18: CREATE TABLE

Each operation uses a separate awaited query. Values are passed through node-postgres parameters such as $1 and $2; the table and constraint names are fixed in the SQL. node-postgres: Queries

import pg from 'pg';

const client = new pg.Client({ connectionTimeoutMillis: 5000 });
const validate = `ALTER TABLE pg_temp.article_links
  VALIDATE CONSTRAINT article_links_article_fk`;

async function expectForeignKeyError(label, sql, values = []) {
  try {
    await client.query(sql, values);
  } catch (error) {
    if (error.code !== '23503' ||
        error.constraint !== 'article_links_article_fk') throw error;
    console.log(label + ': 23503 (expected)');
    return;
  }
  throw new Error(label + ': unexpectedly succeeded');
}

async function showStatus(label) {
  const result = await client.query(`
    SELECT conname, convalidated
    FROM pg_constraint
    WHERE conrelid = 'pg_temp.article_links'::regclass
      AND conname = 'article_links_article_fk'
      AND contype = 'f'
  `);
  console.log(label, result.rows);
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows);
  await client.query(`CREATE TEMP TABLE articles (
    article_id integer PRIMARY KEY,
    title text NOT NULL
  )`);
  await client.query(`CREATE TEMP TABLE article_links (
    link_id integer PRIMARY KEY,
    article_id integer,
    label text NOT NULL
  )`);
  await client.query(
    'INSERT INTO pg_temp.articles VALUES ($1, $2)',
    [1, 'Desk arrangement']
  );
  await client.query(`INSERT INTO pg_temp.article_links VALUES
    (10, 1, 'Matching article'),
    (11, 99, 'Older unmatched reference')`);
  await client.query(`ALTER TABLE pg_temp.article_links
    ADD CONSTRAINT article_links_article_fk
    FOREIGN KEY (article_id)
    REFERENCES pg_temp.articles (article_id) NOT VALID`);
  await showStatus('After adding');

  await expectForeignKeyError(
    'New unmatched reference',
    'INSERT INTO pg_temp.article_links VALUES ($1, $2, $3)',
    [12, 99, 'New reference']
  );
  await expectForeignKeyError(
    'Changed unmatched reference',
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [98, 11]
  );
  await client.query(
    'UPDATE pg_temp.article_links SET label = $1 WHERE link_id = $2',
    ['Revised label', 11]
  );
  console.log('Label-only update succeeded');

  const unmatched = await client.query(`
    SELECT l.link_id, l.article_id, l.label
    FROM pg_temp.article_links AS l
    WHERE l.article_id IS NOT NULL
      AND NOT EXISTS (
        SELECT 1 FROM pg_temp.articles AS a
        WHERE a.article_id = l.article_id
      )
    ORDER BY l.link_id
  `);
  console.log('Unmatched references', unmatched.rows);
  await expectForeignKeyError('Validation before repair', validate);
  await showStatus('After failed validation');

  await client.query(
    'UPDATE pg_temp.article_links SET article_id = $1 WHERE link_id = $2',
    [1, 11]
  );
  await client.query(validate);
  await showStatus('After successful validation');
} finally {
  await client.end();
}

Run the saved program from the same project directory:

node not-valid-demo.mjs

The JavaScript passed a Node syntax check during preparation. Database execution was unavailable in the research environment; the following results are expected from the documented behavior, rather than a captured execution log.

How should you interpret the results?

First, the constraint should appear with convalidated set to false. The catalog defines that field as whether the constraint has been validated. The query filters by both table and constraint name because constraint names are not necessarily unique across the database. PostgreSQL: pg_constraint

Next, the new unmatched insert and changed unmatched reference should each produce 23503. PostgreSQL identifies that SQLSTATE as foreign_key_violation and recommends checking error codes rather than matching localized message text. The helper accepts only the expected code and constraint; unexpected failures stop the program. PostgreSQL error codes

The label-only update leaves link 11 pointing to article 99. The diagnostic query should therefore return exactly that link, now labeled Revised label. Its explicit IS NOT NULL condition excludes optional null references, while NOT EXISTS identifies non-null IDs absent from articles. These are illustrative results derived from the supplied synthetic rows.

Validation should fail at that point. After the repair changes link 11 to reference article 1, validation should succeed and convalidated should become true. VALIDATE CONSTRAINT checks the previously unvalidated constraint by scanning the table; it performs no repair itself. PostgreSQL: ALTER TABLE

The repair is an explicit editorial choice for this demonstration: both links are intended to reference Desk arrangement. For your own article data, review each unmatched reference before choosing whether to correct the ID, supply the missing article, or remove an obsolete link. Automatically redirecting every unmatched ID to one article would conceal distinctions the database cannot resolve.

How do you apply the sequence to populated tables?

Use three separate stages: add and commit the NOT VALID constraint, inspect and repair unmatched references, then validate. Adding the foreign key takes a SHARE ROW EXCLUSIVE lock on both tables. Validation takes SHARE UPDATE EXCLUSIVE on the referencing table and ROW SHARE on the referenced table, allowing concurrent ordinary updates during the validation scan. Lock acquisition can still wait. PostgreSQL: ALTER TABLE

Keeping the initial addition and later validation in separate transactions preserves the purpose of the staged approach: the initial transaction can finish before the longer scan begins. Schedule the addition as a short operation and treat the diagnostic query as a cleanup aid, with successful validation as the completion step.

The demonstration deliberately avoids an explicit transaction block so that expected statement failures can be handled individually. If you adapt the sequence to node-postgres transactions, issue BEGIN, COMMIT, and ROLLBACK yourself, and keep every statement in a transaction on the same client. The node-postgres documentation explicitly says not to use pool.query() for transaction statements. node-postgres: Transactions

Use the example’s three observations together: unmatched rows identify the repair work, rejected new references demonstrate ongoing enforcement, and convalidated = true records successful validation. That gives you a concrete stopping point for adding the relationship to populated article tables.

Sources