A composite PostgreSQL foreign key needs an eligible unique key covering the referenced columns. A primary key on id alone does not qualify a reference to (id, edition), even though it logically makes that pair unique. PostgreSQL: CREATE TABLE

This guide helps a developer working in Terminal on a Mac diagnose that mismatch, choose the appropriate relationship, and test the result with node-postgres. All example rows describe synthetic articles. The SQL targets PostgreSQL 18 and ordinary equality-based foreign keys; PostgreSQL 18 documents separate rules for temporal references using PERIOD. PostgreSQL: CREATE TABLE

Why does PostgreSQL reject the composite reference?

Suppose an article table has three columns: id, edition, and title. Its primary key is id. Another table stores an article reference as two values: article_id and edition.

The intended relationship is expressed by this table-constraint fragment:

FOREIGN KEY (article_id, edition)
  REFERENCES demo_articles (id, edition)

That declaration asks PostgreSQL to check the two values together against one parent row. The documented target must be the columns of a non-deferrable unique or primary key constraint, or the columns of a non-partial unique index. The existing primary key covers only id; the reference names (id, edition). PostgreSQL: CREATE TABLE

The mathematical reasoning behind the objection is understandable: if no two parent rows can share an id, they cannot share an (id, edition) pair either. Nevertheless, that implication does not supply the matching declared target required by the foreign-key definition. For this schema, adding UNIQUE (id, edition) supplies it. The extra constraint is logically redundant for uniqueness while serving a concrete purpose as the composite reference target.

Consider these illustrative parent rows:

id edition title
101 morning Paper Shapes
102 evening Color Notes

A child pair of (101, 'morning') has a match. The pair (101, 'evening') does not: its two values appear in the table, but in different rows. This distinction is the reason to use a composite relationship when both stored values must describe the same article.

Should you add the unique key or simplify the foreign key?

Choose the relationship before changing the schema. The right question is whether the child needs to store and enforce an edition alongside the article identifier.

Required behavior Suitable design Consequence
A child identifies an article and displays its current edition Store article_id; reference the parent id Read the edition from the parent when querying
A child stores an edition that must agree with its article Keep the composite reference; add UNIQUE (id, edition) to the parent PostgreSQL checks that the stored pair exists together
The same article number can identify several edition rows Model the parent identity as (id, edition) A primary key on id alone would prevent those parent rows

The last row describes a different data model. Do not remove the single-column primary key simply to make an error disappear: first decide whether repeated article numbers actually belong in the parent table. PostgreSQL permits a composite primary key, and a table can have only one primary key. PostgreSQL: Constraints

For the runnable example below, article identifiers remain globally unique. The child deliberately stores an edition too, so the parent retains PRIMARY KEY (id) and gains the additional unique pair.

How can you reproduce and fix the error on a Mac?

Use an existing local PostgreSQL 18 database and a working Node.js installation; this walkthrough assumes both are already available. Open Terminal and create a new project directory. Install the driver with the documented package command. node-postgres: Welcome

mkdir composite-fk-demo
cd composite-fk-demo
npm init -y
npm install pg

Save the following complete program as demo.mjs. It creates temporary tables, demonstrates the rejected schema, adds the matching key, and tests valid and invalid article pairs. All statements use one client because node-postgres requires a transaction to stay on the same client instance. node-postgres: Transactions

The helper uses a savepoint around each expected failure so the transaction can continue. PostgreSQL documents ROLLBACK TO SAVEPOINT as undoing commands after that savepoint and allowing work to resume. PostgreSQL: ROLLBACK TO SAVEPOINT

import pg from 'pg';
const { Client } = pg;
const client = new Client();

const childDDL = `CREATE TEMP TABLE demo_article_links (
  link_id integer PRIMARY KEY,
  article_id integer NOT NULL,
  edition text NOT NULL,
  FOREIGN KEY (article_id, edition)
    REFERENCES demo_articles (id, edition)
)`;

async function expectError(sql, values, code) {
  await client.query('SAVEPOINT expected_error');
  try {
    await client.query(sql, values);
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT expected_error');
    await client.query('RELEASE SAVEPOINT expected_error');
    if (error.code !== code) throw error;
    console.log(`Expected error: ${code}`);
    return;
  }
  await client.query('ROLLBACK TO SAVEPOINT expected_error');
  await client.query('RELEASE SAVEPOINT expected_error');
  throw new Error(`Expected SQLSTATE ${code}, but query succeeded`);
}

try {
  await client.connect();
  await client.query('BEGIN');
  await client.query(`CREATE TEMP TABLE demo_articles (
    id integer PRIMARY KEY,
    edition text NOT NULL,
    title text NOT NULL
  )`);

  await expectError(childDDL, [], '42830');

  await client.query(`ALTER TABLE demo_articles
    ADD CONSTRAINT demo_articles_id_edition_key
    UNIQUE (id, edition)`);
  await client.query(childDDL);

  await client.query(`INSERT INTO demo_articles (id, edition, title)
    VALUES ($1, $2, $3), ($4, $5, $6)`,
    [101, 'morning', 'Paper Shapes', 102, 'evening', 'Color Notes']);

  await client.query(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [1, 101, 'morning']);

  await expectError(`INSERT INTO demo_article_links
    (link_id, article_id, edition) VALUES ($1, $2, $3)`,
    [2, 101, 'evening'], '23503');

  const result = await client.query(`SELECT l.link_id, a.title, a.edition
    FROM demo_article_links AS l
    JOIN demo_articles AS a
      ON a.id = l.article_id AND a.edition = l.edition
    ORDER BY l.link_id`);
  console.table(result.rows);
} catch (error) {
  console.error(error.code ?? error.message);
  process.exitCode = 1;
} finally {
  try { await client.query('ROLLBACK'); }
  finally { await client.end(); }
}

The program passes article values separately through $1, $2, and subsequent placeholders, following the driver's parameterized-query API. Table and column names remain fixed in the SQL; PostgreSQL parameters cannot stand for identifiers. node-postgres: Queries

Run the program with connection settings for your existing local database. This invocation assumes the database is named article_demo, the server listens on port 5432, and your existing default local connection settings work. Change the database name or port if needed. node-postgres reads the PostgreSQL connection environment variables. node-postgres: Connecting

PGHOST=localhost PGPORT=5432 PGDATABASE=article_demo node demo.mjs

The JavaScript passed a syntax check during preparation of this article. The database behavior was checked against the documentation; the program was not executed against a PostgreSQL server here.

What do the two errors and the final query demonstrate?

With the stated prerequisites, the expected messages are:

Expected error: 42830
Expected error: 23503

The first error occurs while creating the child table, before any article rows are inserted. PostgreSQL identifies 42830 as invalid_foreign_key. After the parent gains the matching unique key, the same child-table definition becomes valid. PostgreSQL: Error Codes

The second error occurs while inserting (101, 'evening'). The schema is now valid, but the supplied pair has no parent row. PostgreSQL identifies 23503 as foreign_key_violation. Checking the code distinguishes this row mismatch from the earlier schema mismatch without depending on localized error text. PostgreSQL: Error Codes

The final query should return one row: link 1, title Paper Shapes, edition morning. Its join condition includes both components of the relationship. That makes the query describe the same pair that the constraint enforces.

Two separate foreign keys would express a different rule. Even if edition had its own eligible unique key, independent references to id and edition would allow each value to match a different parent row. The worked example makes that difference visible: both individual values in the rejected pair exist, but their combination does not.

The final ROLLBACK deliberately discards the demonstration, including its table definitions. To adapt the design to persistent tables, make the schema change in your normal migration process; copying this temporary-table program does not create a lasting application schema.

What should you check before changing an existing schema?

Start with the referenced column list. Identify the parent key that actually covers that list, rather than relying on an index name or on the fact that the current rows happen to be distinct. For this example, the needed addition is the named UNIQUE (id, edition) constraint shown in the program.

Adding a unique constraint creates a unique B-tree index. Account for that additional index when choosing between the composite design and storing only article_id. The foreign-key declaration itself does not automatically create an index on the child columns. PostgreSQL: Constraints

Before adding a foreign key to an existing child table, check whether its stored pairs match parent rows. A valid target key solves the schema requirement; it does not repair mismatched child values. Ordinary ALTER TABLE ... ADD CONSTRAINT checks existing rows. PostgreSQL also supports adding a foreign key as NOT VALID and later running VALIDATE CONSTRAINT; that stages the existing-row check while applying the constraint to subsequent inserts or updates. PostgreSQL: ALTER TABLE

Treat validation as a separate issue from target eligibility. NOT VALID does not supply the missing parent key. It also is not a way to add a unique constraint without proving uniqueness: PostgreSQL 18 limits that option to foreign-key, check, and not-null constraints. PostgreSQL: ALTER TABLE

Which boundaries can change the result?

A partial unique index does not qualify as the ordinary foreign-key target described here. Likewise, making the referenced unique constraint deferrable does not satisfy the documented non-deferrable constraint requirement. Use the straightforward default UNIQUE (id, edition) for this example. PostgreSQL: CREATE TABLE

Null handling also matters. Under the default matching behavior, a composite foreign key does not require a parent match when any referencing component is null. The example marks both child components NOT NULL so every stored pair must match. If an optional relationship is intended, MATCH FULL permits an all-null reference while rejecting a partially null one. PostgreSQL: Constraints

The useful test is the pair itself: can the child name an article identifier and an edition that belong to different rows? If that must fail, keep the composite relationship and give it a matching parent key. If the child only needs to identify an article, reference id and obtain the edition from that article when querying.

Sources