473,698 Members | 2,080 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 3926
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
2097
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
1805
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
1548
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
1420
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
2433
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
1678
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
1780
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
3459
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
2793
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
8672
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8600
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
9156
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
7712
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...
0
4361
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
4614
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3038
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
2
2323
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1998
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.