Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases > #435 > unrolled thread

Q: is INDEX synonymous with KEY in SQL92?

Back to article view | Back to comp.databases

Contents

  Q: is INDEX synonymous with KEY in SQL92? jmichae3@yahoo.com - 2013-09-27 16:54 -0700
    Re: Q: is INDEX synonymous with KEY in SQL92? Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2013-09-30 14:55 +0200
    Re: Q: is INDEX synonymous with KEY in SQL92? Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2013-09-30 16:03 +0200
    Re: Q: is INDEX synonymous with KEY in SQL92? Norbert_Paul <norbertpauls_spambin@yahoo.com> - 2013-10-03 11:16 +0200
      Re: Q: is INDEX synonymous with KEY in SQL92? Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2013-10-03 12:05 +0200
        Re: Q: is INDEX synonymous with KEY in SQL92? Norbert_Paul <norbertpauls_spambin@yahoo.com> - 2013-10-03 13:48 +0200
          Re: Q: is INDEX synonymous with KEY in SQL92? Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2013-10-03 15:51 +0200

#435 — Q: is INDEX synonymous with KEY in SQL92?

for the syntax 
CREATE TABLE t (
abc VARCHAR(200) NOT NULL DEFAULT '' UNIQUE INDEX
)
I understand you can have the syntax UNIQUE INDEX or UNIQUE KEY in mysql.
question is, (I just want to verify correctness) whether INDEX and KEY are synonymous here, or whether KEY refers to a FOREIGN KEY or PRIMARY KEY that I might create.
I am not asking someone to actually try this code, I can tell you this does not work in mysql, but it is in the documentation.

I am just trying to clarify my understanding of the definition of "KEY" with regards to UNIQUE KEY.
maybe they do mean the same thing?
thanks.

[toc] | [next] | [standalone]

#436

On 09/28/2013 01:54 AM, jmichae3@yahoo.com wrote:
> for the syntax 
> CREATE TABLE t (
> abc VARCHAR(200) NOT NULL DEFAULT '' UNIQUE INDEX
> )
> I understand you can have the syntax UNIQUE INDEX or UNIQUE KEY in mysql.
> question is, (I just want to verify correctness) whether INDEX and KEY are synonymous here, or whether KEY refers to a FOREIGN KEY or PRIMARY KEY that I might create.
> I am not asking someone to actually try this code, I can tell you this does not work in mysql, but it is in the documentation.
> 
> I am just trying to clarify my understanding of the definition of "KEY" with regards to UNIQUE KEY.
> maybe they do mean the same thing?
> thanks.
> 

MySQL treats KEY and INDEX as synonyms which is somewhat confusing. I
haven’t found any references to INDEX in SQL92 (nor later versions). It
is my impression that indexes is left as an exercise for the implementer
of a DBMS.

You can find a list of reserved words at:

http://developer.mimer.se/standard/reservedwords/sql-reserved-words.tml

There is also a validator for SQL92, SQL99 and SQL2003 at:

http://developer.mimer.se/validator/index.htm


Draft documents of various SQL standards can be found at:

http://www.wiscorp.com/SQLStandards.html

[toc] | [prev] | [next] | [standalone]

#437

jmichae3@yahoo.com, 28.09.2013 01:54:
> for the syntax 
> CREATE TABLE t (
> abc VARCHAR(200) NOT NULL DEFAULT '' UNIQUE INDEX
> )
>
> I understand you can have the syntax UNIQUE INDEX or UNIQUE KEY in
> mysql. question is, (I just want to verify correctness) whether INDEX
> and KEY are synonymous here, or whether KEY refers to a FOREIGN KEY
> or PRIMARY KEY that I might create. I am not asking someone to
> actually try this code, I can tell you this does not work in mysql,
> but it is in the documentation.
> 
> I am just trying to clarify my understanding of the definition of
> "KEY" with regards to UNIQUE KEY. maybe they do mean the same thing? 
> thanks.

The SQL standard does not specify anything about indexes. 
However there is also no KEY "option" for the create table statement.

A table definition (create table) consists of:

<table definition> ::=
    CREATE [ <table scope> ] TABLE <table name> <table contents source>
    [ ON COMMIT <table commit action> ROWS ]

So the <table contents source> is what we are interested in:

<table contents source> ::=
    <table element list>
  | <typed table clause>
  | <as subquery clause>

Let's look at the <table element list>

<table element list> ::=
    <left paren> <table element> [ { <comma> <table element> }... ] <right paren>

<table element> ::=
    <column definition>
  | <table constraint definition>
  | <like clause>
 
There are two places where we could expect a "UNIQUE" or "KEY" "constraint: <column definition> and <table constraint definition>

<column constraint definition> ::=
    [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]

<column constraint> ::=
     NOT NULL
   | <unique specification>
   | <references specification>
   | <check constraint definition>

As MySQL does neither support inline FK constraints nor check constraint the only possible option is the the <unique specification> which is defined as

<unique specification> ::=
    UNIQUE
  | PRIMARY KEY

The other possible place is the  <table constraint definition> which is defined as:

<table constraint definition> ::=
   [ <constraint name definition> ] <table constraint>
   [ <constraint characteristics> ]

<table constraint> ::=
    <unique constraint definition>
  | <referential constraint definition>
  | <check constraint definition>

The <unique constraint definition> is defined as:

<unique constraint definition> ::=
     <unique specification> <left paren> <unique column list> <right paren>
   | UNIQUE ( VALUE )

<unique specification> ::=
    UNIQUE
  | PRIMARY KEY

So the standard does not define any possibility that would allow "KEY", "UNIQUE KEY" or "UNIQUE" index in the table definition (unless I overlooked something in that huge document).

[toc] | [prev] | [next] | [standalone]

#438

jmichae3@yahoo.com wrote:
> for the syntax
> CREATE TABLE t (
> abc VARCHAR(200) NOT NULL DEFAULT '' UNIQUE INDEX
> )
> I understand you can have the syntax UNIQUE INDEX or UNIQUE KEY in mysql.
> question is, (I just want to verify correctness) whether INDEX and KEY are synonymous here, or whether KEY refers to a FOREIGN KEY or PRIMARY KEY that I might create.
> I am not asking someone to actually try this code, I can tell you this does not work in mysql, but it is in the documentation.
>
> I am just trying to clarify my understanding of the definition of "KEY" with regards to UNIQUE KEY.
> maybe they do mean the same thing?
> thanks.

In relational database theory a unique index is automatically a key (by definition of "key").
 From the, possibly many, keys you pick the one which pleases you most and call it the "primary key".
Ususally this primary key determines the organization of the file that represents the database table
whereas the indexes, be they unique or not, are secondary structures (files) which accompany the
table (file).

Norbert

[toc] | [prev] | [next] | [standalone]

#439

Norbert_Paul wrote on 03.10.2013 11:16:
> Ususally this primary key determines the organization of the file that represents the database table

The "usually" part of that statement is not true.

You are probably referring to the concept of a "clustered index" as e.g. MySQL or SQL Server are using, but not all DBMS work that way.

[toc] | [prev] | [next] | [standalone]

#440

Harry Tuttle wrote:
> Norbert_Paul wrote on 03.10.2013 11:16:
>> Ususally this primary key determines the organization of the file that
>> represents the database table
>
> The "usually" part of that statement is not true.
>
> You are probably referring to the concept of a "clustered index" as e.g.
> MySQL or SQL Server are using, but not all DBMS work that way.

No. I am just referring to what I learned at university on database
implementation. See for example p. 60 in
http://wwwiti.cs.uni-magdeburg.de/iti_db/lehre/db2_en/db2-md-english.pdf

Note that the majority (MySQL, SQL Server) of your examples
(MySQL, SQL Server, "not all DBMS") also works that way. Hence your exaples
support "usually" whereas "all" would have been wrong.

Cheers
NP

[toc] | [prev] | [next] | [standalone]

#441

Norbert_Paul wrote on 03.10.2013 13:48:
> No. I am just referring to what I learned at university on database
> implementation. See for example p. 60 in
> http://wwwiti.cs.uni-magdeburg.de/iti_db/lehre/db2_en/db2-md-english.pdf

I assume you are referring to slide 2-30 and 2-31

Those statements seem to be strongly influenced by the way SQL Server (or MySQL) implement a primary key.

> Note that the majority (MySQL, SQL Server) of your examples
> (MySQL, SQL Server, "not all DBMS") also works that way. Hence your exaples
> support "usually" whereas "all" would have been wrong.

My "example" was not an "example" it was what I assumed you had in mind.

e.g. Oracle, Postgres, DB2, Firebird, Informix, Vertica, Ingres and Cubrid do not distinguish between a "primary index" and a "secondary" index
i.e. they don't automatically create a clustered index on the primary key - not all of them actually have such a thing as a clustered index.

Teradata does have the notion of a "primary index" but AFAIK that is not a clustered index, it's just the one created to back the primary key definition.

Oracle and DB2 do have a concept of a "clustered index", but they don't automatically create one when you define a primary key constraint

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases

csiph-web