fporto wrote:
> Narayanan-4 wrote:
>
>> fporto wrote:
>>
>>> Hi,
>>> I'm receiving the following message when trying to drop a table:
>>>
>>> ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object
>>> 'SQL080516103912600' because CONSTRAINT 'SQL080525110646121' is dependent
>>> on
>>> that object.
>>>
>>> I would like to obtain the list of constraints associated to each of my
>>> tables.
>>> I've tried to use the Describe SQL command but it doesn't print
>>> constraint
>>> info.
>>>
>>> I've also tried to query system tables:sysconstraints, sysdepends but
>>> couldn't find the objects referred to in the msgs.
>>>
>>> Can someone there help me on this?
>>>
>>> cheers,
>>> Fabio.
>>>
>>>
>>>
>> I tried this
>>
>> select CONSTRAINTNAME from sys.SYSCONSTRAINTS,sys.systables where
>> sys.systables.tablename='NARAYANAN' and
>> sys.sysconstraints.tableid=sys.systables.tableid;
>>
>> Replace the tablename with the name of the table for which you want to
>> find the constraints.
>>
>> Narayanan
>>
>>
>>
>
> Thanks Narayanan,
>
> in fact when I query the table sys.sysconstraints the refered constraint id
> is not there:
>
> select * from sys.sysconstraints where constraintid= 'SQL080525110646121';
> CONSTRAINTID |TABLEID
> |CONSTRAINTNAME
> |&|SCHEMAID |&|REFERENCEC&
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0 rows selected
>
> --
>
> It seems to me that there is some dangling reference. In fact, this error
> message appears when dropping the table databases:
>
> ij> describe databases;
> COLUMN_NAME
> |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
> ------------------------------------------------------------------------------
> DBID |DECIMAL |0 |10 |4 |NULL |NULL |NO
> HOSTSHORT |VARCHAR |NULL|NULL|20 |NULL |40 |YES
> IRI |VARCHAR |NULL|NULL|100 |NULL |200 |YES
> DRIVERNAME |VARCHAR |NULL|NULL|150 |NULL |300 |YES
> DATABASETYPE |DECIMAL |0 |10 |2 |NULL |NULL |YES
> SERVERID |NUMERIC |0 |10 |2 |NULL |NULL |YES
>
> 6 rows selected
> ij> drop table databases;
> ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object
> 'SQL080516103925800' because CONSTRAINT 'SQL080525110734830' is dependent on
> that object.
>
> ij> select * from sys.sysconstraints where constraintid=
> 'SQL080525110734830';
> CONSTRAINTID |TABLEID
> |CONSTRAINTNAME
> |&|SCHEMAID |&|REFERENCEC&
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0 rows selected.
>
> Fabio.
>
Can you confirm that the table drop operation did not succeed? Not sure
how a drop constraint operation
can fail but the constraints will not be present.
Narayanan
|