473,722 Members | 2,338 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Is KEY and INDEX the same thing?

I am following a book on PHP and MySQL and have come across the below SQL
statement.

CREATE TABLE users (
user_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(40) NULL,
password VARCHAR(16) NOT NULL,
registration_DA TE DATETIME NOT NULL,
PRIMARY KEY (user_id),
UNIQUE KEY username (username),
KEY first_name (first_name),
KEY last_name (last_name),
KEY password (password),
);

The author seems to switch from using INDEX to KEY in different chapters
without documenting why but they appear to do the same thing.

Could someone set me straight on this.

Cheers

Phil
Oct 28 '05 #1
3 3930
Yes, they are the same.

Markus
Oct 28 '05 #2
Phil Latio wrote:
I am following a book on PHP and MySQL and have come across the below SQL
statement.

CREATE TABLE users ( ....
PRIMARY KEY (user_id),
UNIQUE KEY username (username),
KEY first_name (first_name),
KEY last_name (last_name),
KEY password (password),
);

The author seems to switch from using INDEX to KEY in different chapters
without documenting why but they appear to do the same thing.


The syntax is a bit inconsistent, in my opinion, but in some
circumstances, the INDEX and KEY keywords can be used interchangeably .
In other circumstances, either INDEX or KEY is the only word that works.

For example:

legal: PRIMARY KEY
not legal: PRIMARY INDEX

legal: FOREIGN KEY
not legal: FOREIGN INDEX

legal: UNIQUE KEY
legal: UNIQUE INDEX

legal: CREATE INDEX ...
not legal: CREATE KEY ...

Anyway, it's a bit confusing. Refer to the reference documentation if
in doubt about the syntax:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

Also, strictly speaking, "key" is a logical concept in relational
databases; it's a column or set of columns that identifies a record.
"Index" is a physical entity in a database that is frequently used to
improve performance, enforce uniqueness efficiently, perform quick
lookups, etc. It is not logically related to the concept of a key, but
they are often used together. An RDBMS could support keys without using
indexes, but it would work very slowly. For that reason, in many RDBMS
implementations , declaring a column as a key implicitly creates an index.

Regards,
Bill K.
Oct 28 '05 #3
> The syntax is a bit inconsistent, in my opinion, but in some
circumstances, the INDEX and KEY keywords can be used interchangeably .
In other circumstances, either INDEX or KEY is the only word that works.

For example:

legal: PRIMARY KEY
not legal: PRIMARY INDEX

legal: FOREIGN KEY
not legal: FOREIGN INDEX

legal: UNIQUE KEY
legal: UNIQUE INDEX

legal: CREATE INDEX ...
not legal: CREATE KEY ...

Anyway, it's a bit confusing. Refer to the reference documentation if
in doubt about the syntax:
http://dev.mysql.com/doc/refman/5.0/...ate-table.html

Also, strictly speaking, "key" is a logical concept in relational
databases; it's a column or set of columns that identifies a record.
"Index" is a physical entity in a database that is frequently used to
improve performance, enforce uniqueness efficiently, perform quick
lookups, etc. It is not logically related to the concept of a key, but
they are often used together. An RDBMS could support keys without using
indexes, but it would work very slowly. For that reason, in many RDBMS
implementations , declaring a column as a key implicitly creates an index.

Regards,
Bill K.


Thanks very much for your excellent explanation (and to Markus aswell).

I have been searching the web this afternoon for articles relating KEY or
INDEX and nothing compares to above.

Cheers

Phil
Oct 28 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2102
by: TheKeith | last post by:
I'm just learning php and set up a sample mysql db to practice with. I have the following script and cannot for the life of me figure out why it is printing each field of the row twice? I checked my db and it only contains the entries once. What is causing this?! ----------------------------------------------------------------------- <?php $con_id = mysql_connect('localhost','dboard_mbr','pa1ss2wo3rd'); ...
3
1809
by: sparks | last post by:
All we are hearing at work now is we should give up on access. NO NO NO you need to go with foxpro. access is dead and can't do anything compared to foxpro It can make coffee, and forward your email....bla bla bla to the people who have used both...what do you say to these people. who have never used or programmed in either one.
6
1551
by: Just Me | last post by:
As I read the documentation sometimes it appears that "type" and "class" mean the same thing. Other times it appears that they do not. Under "Classes" mostly they use the word "class" but I also see: sealed Specifies that another type cannot be derived from this type. and Nested classes also have member characteristics. For more information,
1
1422
by: BrianProgrammer | last post by:
I have this code below, that works like a champ, but two lines are continually marked as obsolete. See embeded notes. Private Shared Function TransformHTMLString(ByVal XSLT As String, _ ByVal XHTML As String) _ As String Dim xdoc As Xml.XmlDocument = New Xml.XmlDocument xdoc.LoadXml(XHTML) Dim xsDoc As Xml.XmlDocument = New XmlDocument
46
2439
by: Your Uncle | last post by:
About a month ago, Heathfield posted the peudosource for random permuting from TAOCP. It was all of maybe five lines. You needed to be able to do two things: be able to get a random number in a range and swap. I remembered that Dan Pop taught me to write the swap as a macro. With forum improvements, this became: #define SWAP(m, n) (tmp = (m), (m) = (n), (n) = tmp) The random number comes, again with forum improvements: int...
2
1680
by: NDayave | last post by:
How do, A while ago i had the problem of backing up a Access 2002 table with unique data that changed in some tables but not others, resulting in restore failure. This was sorted by NeoPa with the (Modified) SQL code: INSERT INTO tblBookings (, , , , Transport )
1
1787
by: mandarseo | last post by:
Hi, Are Service Oriented Architecture and Software as a Service two sides of a same coin? I have heard that both are essential to webify your application so just wondered if they are same. With regards, Mandar Thosar
1
3463
by: Gav | last post by:
Within my application I have a TabControl and I am adding TabPages in dynamically at run time. My problem is that I am able to create the same TabPage twice, ie. I click on button 1 it creates TabPage 1 and adds it to the TabControl with the Tab text being 1. I click on button 1 again it creates TabPage 1 and adds it to the TabControl with the Tab text being 1. So now I hve 2 of the same thing. Is it possible to see if a TabPage already...
40
2797
by: Boltar | last post by:
Hi Why - using gcc on linux - does this return 0 in C but returns 1 in C+ +? I don't get it. #include <stdio.h> struct foo { };
0
8739
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9238
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9088
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8052
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6681
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5995
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4502
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.