Thread: BUG #15451: Error adding foriegn key constraint

The following bug has been logged on the website:

Bug reference:      15451
Logged by:          Harry Clarke
Email address:      harry@harryclarke.me.uk
PostgreSQL version: 10.5
Operating system:   Windows Server 2008 R2 standard
Description:

When trying to create a table, or to add a foreign key constraint to a
table, an error (see below) is produced. However, both tables are permanent
tables, and there are no temporary tables that I am aware of.

ERROR: constraints on permanent tables may reference only permanent tables

ALTER TABLE bnm.table1
    ADD CONSTRAINT id_fk FOREIGN KEY (bnm_id)
    REFERENCES bnm.table2 (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION;

On 2018-10-22 19:05:51 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      15451
> Logged by:          Harry Clarke
> Email address:      harry@harryclarke.me.uk
> PostgreSQL version: 10.5
> Operating system:   Windows Server 2008 R2 standard
> Description:        
> 
> When trying to create a table, or to add a foreign key constraint to a
> table, an error (see below) is produced. However, both tables are permanent
> tables, and there are no temporary tables that I am aware of.
> 
> ERROR: constraints on permanent tables may reference only permanent tables

Note that unlogged tables are also considered not permanent. Is that it?

Greetings,

Andres Freund

On 2018-Oct-22, PG Bug reporting form wrote:

> ERROR: constraints on permanent tables may reference only permanent tables
> 
> ALTER TABLE bnm.table1
>     ADD CONSTRAINT id_fk FOREIGN KEY (bnm_id)
>     REFERENCES bnm.table2 (id) MATCH SIMPLE
>     ON UPDATE NO ACTION
>     ON DELETE NO ACTION;

Is table2 an unlogged table perchance?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services