db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From fporto <fabio.machadopo...@gmail.com>
Subject Re: How to retrieve the constraints associated to a table
Date Tue, 27 May 2008 08:14:53 GMT


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.
-- 
View this message in context: http://www.nabble.com/How-to-retrieve-the-constraints-associated-to-a-table-tp17456725p17485271.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message