You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by fporto <fa...@gmail.com> on 2008/05/25 13:01:19 UTC
How to retrieve the constraints associated to a table
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.
--
View this message in context: http://www.nabble.com/How-to-retrieve-the-constraints-associated-to-a-table-tp17456725p17456725.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: How to retrieve the constraints associated to a table
Posted by Narayanan <V....@Sun.COM>.
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
Re: How to retrieve the constraints associated to a table
Posted by fporto <fa...@gmail.com>.
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.
Re: How to retrieve the constraints associated to a table
Posted by Rick Hillegas <Ri...@Sun.COM>.
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
Re: How to retrieve the constraints associated to a table
Posted by fporto <fa...@gmail.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.
Re: How to retrieve the constraints associated to a table
Posted by Narayanan <V....@Sun.COM>.
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