I have read the documentation many times, but I am note sure I have understood everything. Can you tell me if all the following affirmations are correct ?
1) the following two ways of creating a table are exactly the same ?: create table employees ( company_id int key not null, emp_id int key not null, name varchar(30) not null, birthdate date null ) create table employees ( company_id int not null, emp_id int not null, name varchar(30) not null, birthdate date null, primary key (company_id, emp_id) ) Is is correct to say that the PRIMARY KEY can be defined either with the "key" keyword at the column level, or with the "primary key (col1, col2, ...)" syntax ? 2) A table is physically stored as a B-tree on disk. The rows are ordered physically with the same order as the PRIMARY KEY, as defined during the creation of the table. The physical ordering of rows on disks is called "clustered index" or "native key" in other database systems. So, with sapdb, the clustered index is always the PRIMARY KEY ? 3) So, is it impossible to define a table with rows having a particular physical order, and a PRIMARY KEY defining another order ? Suppose I have this table: create table employees ( emp_id int not null, name varchar(30) not null, birthdate date null, primary key (emp_id) ) I want to have the rows of this table physically ordered on disk by the field "name". But if I create an index on "name", the physical order of the rows is not changed. It just creates another B-tree for the index, pointing on the real rows of the table. So, the only way I see to achieve this is to do the following : create table employees ( emp_id int not null, name varchar(30) not null, birthdate date null, primary key (name, emp_id) ) create unique index pk_employees on employees (emp_id) Then I insert those data: insert into employees values (20, 'smith',null) insert into employees values (10, 'watson',null) insert into employees values (30, 'holmes',null) Then I display the content of the table with "select * from employees". The result is: 30 holmes <null> 20 smith <null> 10 watson <null> Here, we see that the physical order is alphabetically by name, as I wanted it to be. And the secondary unique index on "emp_id" is acting as the real logical "primary key". Is is right to say that what is defined as "primary key" in sapdb is in fact the "clusted index" (or "physical index", or "native key") of the table, which defines the physical order of the rows on disk ? Is there no other way to force another index than the "primary key" to be the "physical index" ? ********************************************************************** This message is being transmitted to you without prior inspection by Geneva Insurance. The message engages only the person who authored it and in no way is binding on Geneva Insurance. The latter can under no circumstances be held responsible for the contents of this message, neither contractually nor in any other manner. If the case arises, any commitments made by the author of this message in the name of Geneva Insurance have no legal validity unless confirmed in a letter duly signed by persons authorized to represent the company. [EMAIL PROTECTED] ********************************************************************** _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
