Issue Details (XML | Word | Printable)

Key: CORE-1261
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Claudio Valderrama C.
Reporter: Alexandr N. Zamaraev
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

ISQL ignore index and ordering in UNIQUE CONSTRAINT for generate metadate script

Created: 15/May/07 03:05 PM   Updated: 29/Dec/07 05:23 AM
Component/s: ISQL
Affects Version/s: 2.0.1
Fix Version/s: 2.1 Beta 2

Time Tracking:
Not Specified

Environment:
OS WinXP Home Rus + sp2
FB SS v2.0.1 b12855
Issue Links:
Relate
 


 Description  « Hide
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;
...


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Claudio Valderrama C. added a comment - 16/Sep/07 06:42 AM
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));