equivalent to MySQL "KEY" keyword?

[George]

I'm trying to use the MySQL demonstration database Sakila. H2 does
not like the "KEY" keyword, as used below. Is there an H2
equivalent? I'm actually at a loss for what a non-primary key
actually means at all (hint for creating an index, perhaps?) Thanks.


CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),
KEY idx_actor_last_name (last_name)
);
Unknown data type: "IDX_ACTOR_LAST_NAME"; SQL statement:
CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),
KEY idx_actor_last_name (last_name)
) [50004-135] HY004/50004 (Help)

[Rami]

> I'm actually at a loss for what a non-primary key
> actually means at all (hint for creating an index, perhaps?)

This is very understandable since all databases use terminology
carelessly and without much thought put into overall conceptual integrity.
That being said I think the relational theory does not have a concept of
non-unique key.

It only talks about superkeys
http://en.wikipedia.org/wiki/Superkey
and candidate keys
http://en.wikipedia.org/wiki/Candidate_key

And of course of primary keys (that is just a candidate key picked out as the
most representative of them) and foreign keys.

So what could a key without the property of uniqueness possibly mean?
You are quite right at pointing out that it could be interpreted as an alias for
index.

- rami

[Dario Fassi]

Hi,
The KEY form to specify an index at table definition time it's a
propietary implementation detail out of standards.
SQL is a descriptive language even the DDL part.

To get the same result just separate the index creation sentence in an
specific CREATE INDEX sentence like:

CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),

);

CREATE INDEX idx_actor_last_name in actor(last_name);

See, http://www.h2database.com/html/grammar.html#create_index

Regards
Dario

El 02/06/10 09:15, George escribió:

[Dario Fassi]

Hi,

>> I'm actually at a loss for what a non-primary key
>> actually means at all (hint for creating an index, perhaps?)
>
>
> This is very understandable since all databases use terminology
> carelessly and without much thought put into overall conceptual
> integrity.
> That being said I think the relational theory does not have a concept
> of non-unique key.
>

> And of course of primary keys (that is just a candidate key picked out
> as the most representative of them) and foreign keys.
>
> So what could a key without the property of uniqueness possibly mean?
> You are quite right at pointing out that it could be interpreted as an
> alias for index.

Alternative, candidate, secondary keys, etc. are all concepts of
database analysis/design.
And partitioning, sharding, order keys, etc. are implementation details
of physical database organization.

At last their implementations vary from dbm to dbm, but the a common
incarnation of that concepts are INDEXES variants.
The most common (and usable) are INDEX and UNIQUE INDEX , this one is
often used to incarnate a alternative KEY , even if isn't an constraint
as in many dbms.

regards,
Dario

[Thomas Mueller]

Hi,

> CREATE TABLE actor (
>  actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>  first_name VARCHAR(45) NOT NULL,
>  last_name VARCHAR(45) NOT NULL,
>  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
>  PRIMARY KEY  (actor_id),
>  KEY idx_actor_last_name (last_name)
> );

This statement works when using the MySQL compatibility mode. The
documentation is not very clear:
http://h2database.com/html/features.html#compatibility "MySQL":
Creating indexes in the CREATE TABLE statement is allowed.

I will add:

Creating indexes in the CREATE TABLE statement is allowed using
INDEX(..) or KEY(..). Example: create table test(id int primary key,
name varchar(255), key idx_name(name));

The problem is that KEY and INDEX could be used as column names in
other databases. That's why it's not supported in the default mode.

Of course for better compatibility it would be better to change your
application to use the more common CREATE INDEX syntax (as a separate
statement).

Regards,
Thomas