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 3 3926
Yes, they are the same.
Markus
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.
> 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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');
...
|
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.
|
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,
|
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
|
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...
| |
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 )
|
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
|
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...
|
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
{
};
|
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...
|
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,...
| |
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...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |