PostgreSQL Composite Foreign Key ‘columns list must not contain duplicates’

This is my example schema:

CREATE TABLE users (
    userid BIGSERIAL PRIMARY KEY,
    name varchar(25) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE follows (
    userid1 int NOT NULL,
    userid2 int NOT NULL,
    PRIMARY KEY (userid1, userid2),
    FOREIGN KEY (userid1, userid2) REFERENCES users (userid) ON DELETE CASCADE
);

If I run this command, I get:

ERROR: number of referencing and referenced columns for foreign key disagree

And If I add ... REFERENCES users (userid, userid) ON DELETE CASCADE

I get:

ERROR: foreign key referenced-columns list must not contain duplicate

I know it works if I type in each line their respective references, but it would be better to not repeat myself.

How can I achieve that with composite foreign keys using the same dependency?

Answer

Though I prefer the syntax shown by Gordon, if you want a one liner you can compress it as:

CREATE TABLE follows (
    userid1 int NOT NULL REFERENCES users ON DELETE CASCADE,
    userid2 int NOT NULL REFERENCES users ON DELETE CASCADE,
    PRIMARY KEY (userid1, userid2)
);

The down side of this short hand notation is that the FKs don’t have names. It could be tricky to manage them in the future should you need to remove them or modify them. Experience has shown me that it’s better to name them, and for that you need to use the full syntax, as in:

CREATE TABLE follows (
    userid1 int NOT NULL,
    userid2 int NOT NULL,
    PRIMARY KEY (userid1, userid2),
    CONSTRAINT fk1 FOREIGN KEY (userid1) 
      REFERENCES users (userid) ON DELETE CASCADE,
    CONSTRAINT fk2 FOREIGN KEY (userid2) 
      REFERENCES users (userid) ON DELETE CASCADE
);

…just to get the fk1 and fk2 identifiers.