Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <17485271.post@talk.nabble.com>
Date: Tue, 27 May 2008 01:14:53 -0700 (PDT)
From: fporto <fabio.machadoporto@gmail.com>
To: derby-user@db.apache.org
Subject: Re: How to retrieve the constraints associated to a table
In-Reply-To: <483BA465.6060309@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <17456725.post@talk.nabble.com> <483BA465.6060309@sun.com>


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.