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

Groups > comp.databases > #437

Re: Q: is INDEX synonymous with KEY in SQL92?

Show all headers | View raw

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).

Back to comp.databases | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web