You are right, the SYSCONGLOMERATES.CONGLOMERATENAME is different in this particular example and could be used to make a join against SYSCONSTRAINTS.CONSTRAINTNAME when a user does not supply a name for the constraint.
When no name is supplied for the constraint, SYSCONGLOMERATES.CONGLOMERATENAME is same as SYSCONSTRAINTS.CONSTRAINTNAME but when a user does supply a name for the constraint, then SYSCONSTRAINTS.CONSTRAINTNAME has the user supplied name and
SYSCONGLOMERATES.CONGLOMERATENAME has internal generated name. Don't know why we don't just use user supplied name in SYSCONGLOMERATES.CONGLOMERATENAME, that way SYSCONSTRAINTS.CONSTRAINTNAME and SYSCONGLOMERATES.CONGLOMERATENAME
will be always same for a given constraint.
Mamta
On 5/25/06, Daniel John Debrunner <[EMAIL PROTECTED]> wrote:
Mamta Satoor wrote:
> Hi Dan,
>
> Thanks for your alternative suggestions. Seems like option 1 of the 2 ways
> suggested by you might be faster than the 2nd one.
>
> As for DERBY-1343, I don't understand how a check for column
> isconstraint to
> be true can fix the problem. I am copying following sql from
> http://www.nabble.com/When+foreign+key+is+dropped,+is+Derby+dropping+the+wrong+row+from+SYS.SYSCONGLOMERATES--t1654121.html#a4481463
>
> ij> select conglomerateid, conglomeratename, conglomeratenumber, isindex,
> descriptor, isconstraint from sys.sysconglomerates where tableid =
> 8ca44062-010b-50e3-8d63-000000156130';
> CONGLOMERATEID |CONGLOMERATENAME
> |CONGLOMERATENUMBER |ISIN&|DESCRIPTOR |ISCO&
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> a4bcc063-010b-50e3-8d63-000000156130|8ca44062-010b-50e3-8d63-000000156130
> |800 |false|NULL
> |false <--------------------HEAP FOR T2
> 848c0061-010b-50e3-8d63-000000156130|SQL060520012247770
> |817
> |true |UNIQUE BTREE (&|true <--------------------PRIMARY KEY FOR T2
> 848c0061-010b-50e3-8d63-000000156130|SQL060520012250890
> |817 |true |UNIQUE BTREE (&|true <--------------------
> FOREIGN KEY FOR T2
> 3 rows selected
> Both the duplicate rows in SYSCONGLOMERATES table for table T2, have their
> isconstraint set to true, so there is still not a way to uniquely identify
> foreign key conglomerate row from primary key conglomerate row while
> dropping the foreign key constraint.
My mistake, forgot the primary key is a constraint as well. :-(
Though from that example, it looks like the CONGLOMERATENAME is
different, though I guess that's not stored elsewhere.
Dan.
