db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Narayanan <V.Naraya...@Sun.COM>
Subject Re: How to retrieve the constraints associated to a table
Date Tue, 27 May 2008 10:01:06 GMT
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

Mime
View raw message