You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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.
The text was updated successfully, but these errors were encountered: