db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: GROUP BY
Date Mon, 12 Apr 2010 13:03:52 GMT
Knut Anders Hatlen wrote:
> On 04/10/10 05:29 PM, Ян Программист wrote:
>   
>> From my previous post:
>>
>> I created following DDL:
>>
>> create table server_shutdown_type
>> (
>> id integer(3) not null,
>> name varchar(10) not null,
>> primary key(id)
>> Ru);
>> create table server_shutdown_log
>> (
>> record_id integer not null,
>> shutdown_type integer not null,
>> admin_records VARCHAR(30),     
>> primary key (record_id),
>> foreign key (shutdown_type) references server_shutdown_type (id)
>> );
>>   
>> select SYS.SYSCOLUMNS.COLUMNNAME, SYS.SYSCOLUMNS.COLUMNDATATYPE, SYS.SYSCONSTRAINTS.TYPE,
SYS.SYSCONSTRAINTS.REFERENCECOUNT from SYS.SYSCOLUMNS inner join SYS.SYSTABLES on SYS.SYSTABLES.TABLEID
= SYS.SYSCOLUMNS.REFERENCEID INNER JOIN SYS.SYSCONSTRAINTS on SYS.SYSCOLUMNS.REFERENCEID =
SYS.SYSCONSTRAINTS.TABLEID where SYS.SYSTABLES.TABLENAME like 'SERVER%';
>> COLUMNNAME                                                                      
                                               |COLUMNDATATYPE |&|REFERENCEC&
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------
>> ADMIN_RECORDS                                                                   
                                               |VARCHAR(30)    |P|0          
>> ADMIN_RECORDS                                                                   
                                               |VARCHAR(30)    |F|0          
>> RECORD_ID                                                                       
                                               |INTEGER NOT NU&|P|0          
>> RECORD_ID                                                                       
                                               |INTEGER NOT NU&|F|0          
>> SHUTDOWN_TYPE                                                                   
                                               |INTEGER NOT NU&|P|0          
>> SHUTDOWN_TYPE                                                                   
                                               |INTEGER NOT NU&|F|0          
>> ID                                                                              
                                               |INTEGER NOT NU&|P|1          
>> NAME                                                                            
                                               |VARCHAR(10) NO&|P|1
>>
>> Hence columns NAME, ADMIN_RECORDS appear with 'P' value for
>> SYS.SYSCONSTRAINTS.TYPE. But I haven't forced those to be primary keys
>> in DDL. John
>>     
>
> I think the above join only tells that those columns are in a table that
> has a primary key and/or foreign key defined. To find out which columns
> are part of a key, I think you need to inspect the IndexDescriptor
> object in SYS.SYSCONGLOMERATES.DESCRIPTOR, but I'm not sure if you can
> do that from SQL.
>
>   
It might be possible to use a table function to unpack the descriptor.

Hope this helps,
-Rick

Mime
View raw message