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.
