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

ISQL ignore index and ordering in UNIQUE CONSTRAINT for generate metadate script [CORE1261] #1684

Closed
firebird-automations opened this issue May 15, 2007 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @tonal

Assigned to: Claudio Valderrama C. (robocop)

Is related to QA173

Command line:
>isql -u sysdba -p masterkey -x BASE.FDB
Output part:
...
CREATE TABLE COST_METHOD (ID D_ID,
TITLE D_TITLE,
VERS D_VERS,
CONSTRAINT PK_COST_METHOD PRIMARY KEY (ID),
CONSTRAINT UNQ_COST_METHOD_TITLE UNIQUE (TITLE),
CONSTRAINT UNQ_COST_METHOD_VERS UNIQUE (VERS));
...

Bat IBExpert script show:
...
CREATE TABLE COST_METHOD (
ID D_ID /* D_ID = INTEGER NOT NULL */,
TITLE D_TITLE /* D_TITLE = VARCHAR(252) */,
VERS D_VERS /* D_VERS = INTEGER NOT NULL */
);
ALTER TABLE COST_METHOD ADD CONSTRAINT UNQ_COST_METHOD_TITLE UNIQUE (TITLE);
ALTER TABLE COST_METHOD ADD CONSTRAINT UNQ_COST_METHOD_VERS UNIQUE (VERS)
USING DESCENDING INDEX UNQ_COST_METHOD_VERS;
...

Commits: 78e090e b495af9 b261c3d eb51d83

@firebird-automations
Copy link
Collaborator Author

Modified by: Claudio Valderrama C. (robocop)

assignee: Claudio Valderrama C. [ robocop ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Claudio Valderrama C. (robocop)

This is the list of constraints as seen in the system tables from a test db I created:

SQL> set list;
SQL> select * from rdb$relation_constraints order by rdb$constraint_type, rdb$constraint_name;

RDB$CONSTRAINT_NAME INTEG_5

RDB$CONSTRAINT_TYPE NOT NULL
RDB$RELATION_NAME BB

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME <null>

RDB$CONSTRAINT_NAME Z

RDB$CONSTRAINT_TYPE PRIMARY KEY
RDB$RELATION_NAME BB

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME Z

RDB$CONSTRAINT_NAME A

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME BB

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME A

RDB$CONSTRAINT_NAME AC

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME T

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME AI

RDB$CONSTRAINT_NAME AC2

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME T2

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME AC2

RDB$CONSTRAINT_NAME INTEGRAL

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME TEST

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME INTEGRAL

RDB$CONSTRAINT_NAME INTEG_6

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME DUP

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME RDB$3

RDB$CONSTRAINT_NAME INTEG_7

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME T3

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME I3

RDB$CONSTRAINT_NAME INTEG_8

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME NN

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME NN0

RDB$CONSTRAINT_NAME Z1

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME NN

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME NN1

RDB$CONSTRAINT_NAME Z2

RDB$CONSTRAINT_TYPE UNIQUE
RDB$RELATION_NAME NN

RDB$DEFERRABLE NO
RDB$INITIALLY_DEFERRED NO
RDB$INDEX_NAME NN2

This is the new output:

F:\fb2dev\fbbuild\firebird2\temp\debug\firebird\bin>isql CONSTRAINT.FDB -x
Database: CONSTRAINT.FDB

SET SQL DIALECT 3;

/* CREATE DATABASE 'CONSTRAINT.FDB' DEFAULT CHARACTER SET NONE */

/* Table: BB, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE BB (Z INTEGER NOT NULL,
A INTEGER,
CONSTRAINT Z PRIMARY KEY (Z),
CONSTRAINT A UNIQUE (A));

/* Table: DUP, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE DUP (A INTEGER,
UNIQUE (A));

/* Table: NN, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE NN (A INTEGER,
B INTEGER,
C INTEGER,
UNIQUE (A) USING DESCENDING INDEX NN0,
CONSTRAINT Z1 UNIQUE (B) USING INDEX NN1,
CONSTRAINT Z2 UNIQUE (C) USING DESCENDING INDEX NN2);

/* Table: T, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE T (A INTEGER,
CONSTRAINT AC UNIQUE (A) USING DESCENDING INDEX AI);

/* Table: T2, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE T2 (A2 INTEGER,
CONSTRAINT AC2 UNIQUE (A2));

/* Table: T3, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE T3 (A3 INTEGER,
UNIQUE (A3) USING INDEX I3);

/* Table: TEST, Owner: ATENEA\ADMINISTRATOR */
CREATE TABLE TEST (A INTEGER,
CONSTRAINT INTEGRAL UNIQUE (A));

@firebird-automations
Copy link
Collaborator Author

Modified by: Claudio Valderrama C. (robocop)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1 Beta 2 [ 10190 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA173 [ QA173 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12111 ] => Firebird [ 15569 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

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