No index_name for primary keys?

Discussion on a SQL Parser for EasyMOD
Locked
User avatar
-=ET=-
Registered User
Posts: 213
Joined: Mon May 26, 2003 1:35 pm
Location: France

No index_name for primary keys?

Post by -=ET=- » Fri Dec 30, 2005 9:31 pm

markus_petrux just to be sure...

A primary key written like this "PRIMARY KEY ext_id (ext_id)" raised an error.
It's because, according to this document...
viewtopic.php?f=43&t=20911
...we must not give an index_name to a primary key?

Or it's another problem?

Thanks :)
Eternal newbie

markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: No index_name for primary keys?

Post by markus_petrux » Sun Jan 01, 2006 5:39 am

The document might be confusing. I have just figured the syntax for <index_key_definiton> was not completely correct, fixed now.


Ok, the index_name shouldn't be specified for PRIMARY KEYs. It is only allowed (optional) for the other type of indexes (KEY or UNIQUE).

As per the document, the syntax for a PRIMARY KEY is as follows:

Code: Select all

PRIMARY KEY (<index_key_definition>[,...])
That is the string PRIMARY KEY followed by a comma separated list of <index_key_definition> elements, enclosed between parentheses.

The syntax for <index_key_definition> is:

Code: Select all

column_name[(length)] [ASC|DESC]
Where column_name is a SQL identifier that can be optionally followed by a length specifier (depending on the data type of the column), a white space and an order specifier (ASC, which is the default or DESC).


A PRIMARY KEY is like a unique index with the NOT NULL attribute, but it also defines a constraint that can be referenced from other tables. Some SQL engines (that support referencial integrity) allow to define the name of this constraint, but that uses another syntax similar to:

Code: Select all

CONSTRAINT name PRIMARY KEY(foo, bar)
This syntax may vary between SQL engines and we do not use referencial integrity defintions (it is probably very complex to translate between SQL engines) in phpBB databases, so we simply support the tipical way of doing this in MySQL (as described above) and the parser will transform the statement as appropriate. When a constraint name is needed by the parser (thing that could also happen with index names, because it is optional to specify them) the parser generates an identifier derived from the table name.

Maybe you already know some of these things, but I hope it helps to clarify the question. :)

User avatar
-=ET=-
Registered User
Posts: 213
Joined: Mon May 26, 2003 1:35 pm
Location: France

Re: No index_name for primary keys?

Post by -=ET=- » Sun Jan 01, 2006 10:44 am

markus_petrux wrote: Ok, the index_name shouldn't be specified for PRIMARY KEYs.
Thanks! :)
Eternal newbie

Locked