Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SHOW TABLE and ISQL -X loose info about explicitly assigned index for FK [CORE4801] #5099

Open
firebird-automations opened this issue May 18, 2015 · 0 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Test:

recreate table test(
id int
,pid int
,constraint test_id_pk primary key(id) using descending index test_id_desc_pk
,constraint test_fk_pid foreign key(pid) references test(id) using descending index test_pid_desc_fk
);
commit;
show table test;
show index;

-- this is output of SHOW TABLE command:
ID INTEGER Not Null
PID INTEGER Nullable
CONSTRAINT TEST_FK_PID:
Foreign key (PID) References TEST (ID) ----------------------------------- [ 1 ]
CONSTRAINT TEST_ID_PK:
Primary key (ID) uses explicit descending index TEST_ID_DESC_PK

-- this is output of SHOW INDEX command:
TEST_ID_DESC_PK UNIQUE DESCENDING INDEX ON TEST(ID)
TEST_PID_DESC_FK DESCENDING INDEX ON TEST(PID)

(similar in 2.1, 2.5 & 3.0)

Name of index used in FK: TEST_PID_DESC_FK -- occures only in the SHOW INDEX output.
No index name between "Foreign key (PID)" and "References TEST (ID)" but it was explicitly specified in the table DDL (see marked line "[ 1 ]").

ISQL -X result:

SET SQL DIALECT 3;

/* CREATE DATABASE . . .; */
COMMIT WORK;

/* Table: TEST, Owner: SYSDBA */
CREATE TABLE TEST (ID INTEGER NOT NULL,
PID INTEGER,
CONSTRAINT TEST_ID_PK PRIMARY KEY (ID) USING DESCENDING INDEX TEST_ID_DESC_PK);

ALTER TABLE TEST ADD CONSTRAINT TEST_FK_PID FOREIGN KEY (PID) REFERENCES TEST (ID);

-- here also no index specified in ALTER TABLE ADD <FK> statement.

Result will be not good if such database metadata is restored from script, than making data migration and start to analyze performance etc - no correlation between "standard" index name like "RDB$*****" and its table.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant