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 14:06:36 GMT



Rick Hillegas-2 wrote:
> 
> 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.
>>   
> Hi Fabio,
> 
> I think the problem is that you are trying to look up the constraint by 
> its id rather than its name. The original error message was phrased in 
> terms of the constraint's name, not its id. Here is the output of a 
> little script which demonstrates the relationship between the error 
> message and the columns in sys.sysconstraints:
> 
> ij> create table t( a int primary key );
> 0 rows inserted/updated/deleted
> ij> create table s( a int references t( a ) );
> 0 rows inserted/updated/deleted
> ij> drop table t;
> ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 
> 'SQL080527064442130' because CONSTRAINT 'SQL080527064442220' is 
> dependent on that object.
> ij> select c.constraintname, c.constraintid
> from sys.sysconstraints c, sys.systables t
> where t.tableid=c.tableid
> and t.tablename='T';
> CONSTRAINTNAME                                                                      
                                           
> |CONSTRAINTID                       
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SQL080527064442130                                                                  
                                           
> |6839c016-011a-2a9f-9dde-000000121ac0
> 
> 1 row selected
> ij> select c.constraintname, c.constraintid
> from sys.sysconstraints c, sys.systables t
> where t.tableid=c.tableid
> and t.tablename='S';
> CONSTRAINTNAME                                                                      
                                           
> |CONSTRAINTID                       
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SQL080527064442220                                                                  
                                           
> |2057c01b-011a-2a9f-9dde-000000121ac0
> 
> 1 row selected
> 
> Hope this helps,
> -Rick
> 
> 
Hi Rick,
 you're definitively right.
 thanks a lot,

Fabio.  
 

-- 
View this message in context: http://www.nabble.com/How-to-retrieve-the-constraints-associated-to-a-table-tp17456725p17491238.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message