db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: GROUP BY
Date Sat, 10 Apr 2010 17:29:22 GMT
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.

-- 
Knut Anders


Mime
View raw message