PostgreSQL: What is meant by "please specify covering index name"

Question

I wanted to edit the actions in a table. However I get the error message "Please specify covering index name." when I try to edit the FK. How do I fix this?

The table consists of only two columns:

The foreign keys:

category FK:

I think that that error is coming from pgAdmin itself, not from PostgreSQL. What edit are you trying to make? Is the image shown the pre-edit state, or the post-edit state which you are not allowed to save? — jjanes, Apr 26 '20 at 13:29
And what version of both PostgreSQL and pgAdmin4 are you using? — jjanes, Apr 26 '20 at 13:30
The pictures are pre-edit. I want to change the "on delete" action from no action to cascade. — NullOrNotNull, Apr 26 '20 at 13:37
The pgadmin is on 4.20, the install psql version is 11.7 (Raspbian 11.7-0+deb10u1) — NullOrNotNull, Apr 26 '20 at 13:37
Yea I suppose you're right about this being a PG Admin problem .. I deleted the foreign keys, added them, added the actions and saved. Their FK actions are now on cascade. Now, when I try to change the FK properties again I got the same problem again - but it's still set to on cascade. — NullOrNotNull, Apr 26 '20 at 14:08
Same problem here: I cannot delete/modify foreign key using PgAdmin interface, but I can do the same using sql queries with ALTER TABLE. — Giorgio R, Jun 29 '20 at 07:53

cYee · Answer 1 · 2021-01-30T11:48:29.263

36

Temporary workaround: Using pgAdmin

If you have a single foreign key:

Click on edit to expand, and click on it again to collapse, save button should be re-enable again.

If you have multiple foreign keys:

Repeat on every foreign key entry, click edit and "un-edit" for every foreign key, the Save button should be re-enable after every one of it is clicked.

Usually, whenever the error pop up when I want to add anything to the constraint, I will just expand and collapse every foreign key, save button should be working again.

For action,

If you want to add an action, eg: On update/On delete

The error message will pop up again. Don't forget to click on the '+' button to add the column, then repeat above steps(expand, collapse) to clear the error message, then you can save the foreign key. Yeah~ I know it is ugly...

For updating action for existing foreign key

Currently, I don't know any workaround, but the ugly way...

Delete the fkey -> recreate the foreign-key -> update the action -> clear the error message with above steps -> save.

edited Jan 30 '21 at 11:48

answered Jul 07 '20 at 05:07

cYee

1,915
1
16
24

1

This worked for me to clear the error message "please specify covering index name” but not to enable editing of the FK action. As mentioned by @jjanes, I think that action is not possible. – Mark Hansen Jul 13 '20 at 12:40
1

@MarkHansen Did you press the '+' icon to add the column? then add action. I tried, it works for me. For editing, yeah, it is greyed out, I have to delete the key and then create a new key to update the action. – cYee Jul 13 '20 at 14:18
I am glad, it helped you. – cYee Sep 07 '20 at 01:04
1

This should be marked as the correct answer – Vijay B Dec 21 '20 at 03:32
1

It's a glitch in the INTERFACE. – Vectoria Feb 03 '21 at 11:39

Asad · Answer 2 · 2020-09-03T12:08:44.610

11

in our case this helped: before saving "new FK", firstly open "edit options" of "previously created FK", and DO NOT CHANGE anything then return to "newly created FK" edit options, save button magically activated

edited Sep 03 '20 at 12:08

answered Jun 01 '20 at 05:40

Asad

326
2
7

score 6 · Accepted Answer · answered Apr 26 '20 at 14:38

I can't reproduce your problem, as pgAdmin4 won't let me change anything about a constraint (other than its name) in the first place, so I can't get to the point where it would throw me an error like that. All the affordances to make changes are there, but they are all greyed out.

Also, PostgreSQL itself won't let you change an action on a FK constraint (there is simply no variant of "ALTER TABLE" which implements it), so you have to drop and recreate. So it is not surprising pgAdmin4 wont let me model such an action when it can't be done.

I don't know how you are getting it to produce this error, but it is probably a presentation bug in pgAdmin4, it is reporting an error condition as an error, but with a unhelpful message.

score 2 · Answer 4 · answered May 14 '20 at 18:03

I had the same problem. It's really hard to reproduce it.

I needed to add several foreign keys. So I started to add new columns and new foreign keys. At some point, there was this error "Please specify covering index name." after every action in pgAdmin. Maybe I used an incorrect name for a column because I copypasted the names and there may be incorrect symbols, I don't know.

So to fix it I reverted all my updates: deleted all new foreign keys and new columns. And then this error disappeared.

As another solution I thought it is better to just write SQL queries to add some foreign keys.

i guess that @jjanes is simply correct. you cannot edit FKs afterwards - not in PG Admin nor PostgreSQL. Changing it means that you have to delete and recreate it. At least thats what I can confirm from my experience now — NullOrNotNull, May 16 '20 at 19:28

gharik_cz · Answer 5 · 2020-05-22T08:05:48.510

1

What worked in my case - if you manually turn on "Validated?" option on the newly created foreign key and open "edit" on the previously created foreign key, then the message disappears and you can save the constraint. Seems like some kind of bug.

edited May 22 '20 at 08:05

answered May 22 '20 at 07:54

gharik_cz

11
2

score 1 · Answer 6 · answered Jul 01 '20 at 15:33

Here is my solution:

Table -> Property -> Constraints -> Foreign Key -> click delete icon for all foreign key.
Table -> Property -> Constraints -> Foreign Key -> Add again foreign key.

I guess you alter current FK or add new FK that causing this issue.

score 0 · Answer 7 · answered May 20 '20 at 13:40

0

I had the same error. The problem was that a I had another foreign key with the same name in other table. I think postgres tracks the covering index and the name of the foreign key and don't allow the same name. So you have to use another name.

answered May 20 '20 at 13:40

Joel

315
1
10

score 0 · Answer 8 · answered Jun 05 '20 at 01:05

0

I just solved this, according to https://www.pgadmin.org/docs/pgadmin4/4.19/foreign_key_dialog.html the covering index warning is activated when the "Auto FK Index" is selected when creating the foreign key. This is enabled by default. I went through and unchecked it on each of the indexes on the table that was giving me the issue and the message went away.

answered Jun 05 '20 at 01:05

Tim Andrews

13
2

In my case the Auto FK Index toggle switch is disabled and the default is No so I can't change it. – DavidHyogo Aug 23 '20 at 13:45

score 0 · Answer 9 · answered Jun 21 '20 at 14:07

0

The problem happened randomly for me. I just found that If I go to constraints -> primary key tab and click the trash icon and then when confirming delete dialog comes I cancel it and boom, the error disappears and the save button is enabled!

answered Jun 21 '20 at 14:07

Stefano Mtangoo

6,017
6
47
93

score 0 · Answer 10 · answered Nov 22 '20 at 22:00

0

just type the foreign key index name ... few letters fki_index_name (if you want an index generated "auto FK index" or turn it off!

And yes it looks like a bug in pgadmin not postgresql

answered Nov 22 '20 at 22:00

Smart.Man

41
5

score 0 · Answer 11 · answered Dec 11 '20 at 17:30

0

PgAdmin has a browser at left side, select table and then go to Constraints, do a right click on the foreign key that you want to change, after that in properties you will be able to change it.

answered Dec 11 '20 at 17:30

Duarte

1
1

score 0 · Answer 12 · answered Feb 08 '21 at 13:38

Try adding column using query tool:

ALTER TABLE x ADD COLUMN c INT NOT NULL
CONSTRAINT xy_fk_c REFERENCES y (a)

Where: x will be current table name, c will be column that you want to be created, xy_fk_c will be foreign key name, y will be the reference of your foreign table and a will be the referencing column.

PostgreSQL: What is meant by "please specify covering index name"

12 Answers12