Click here to get back home

cant figure out some mysql commands
 Home | NewsGroups | Search | About
Login Password
Register Now!
 mailing.database.mysql    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
cant figure out some mysql commands ancelotp 08-03-2006
Posted by ancelotp on August 3, 2006, 10:09 am
Please log in for more thread options
 hi,
i'm new to sql
i'd appretiate if someone would helpme out with this doudt i have

CODE:
CREATE TABLE entry (
                                uno                        int(6) NOT NULL auto_increment,
                                fname                 varchar(30) NOT NULL,
                                sname                 varchar(30) NOT NULL,
                                email                 varchar(30) NOT NULL ,
                                college        varchar(30) NOT NULL,
                                dob                 varchar(9) NOT NULL,
                                id                varchar(10) NOT NULL,
                                mobile                 varchar(20) NOT NULL,
                                dom                varchar(30) NOT NULL,
                                sex                 varchar(30) NOT NULL,
                                verified         varchar(3) NOT NULL,
                                PRIMARY KEY (uno),
                                UNIQUE uno (uno),
                                KEY uno_2 (uno)
                                )
i totally cant understand the last two lines of code in this create
statement :
UNIQUE uno (uno),
KEY uno_2 (uno)
... got it from an undocumented project i am working on
i need help immediately please help


Posted by Bill Karwin on August 3, 2006, 3:11 pm
Please log in for more thread options
 ancelotp@gmail.com wrote:
> i totally cant understand the last two lines of code in this create
> statement :
> UNIQUE uno (uno),
> KEY uno_2 (uno)

"KEY" is a synonym for "INDEX". It just means that the column `uno` is
indexed.

"UNIQUE" is a unique constraint; all values in the column must be
distinct. This also implies the creation of an index on the `uno` column.

It appears in this case that both of these are redundant and
unnecessary, since you already have the `uno` column declared as a
PRIMARY KEY, which implies both an index and a unique constraint.

But in some older versions of MySQL, you had to explicitly create an
index on a column before declaring a foreign key constraint on that
column. In more recent versions, declaring a FOREIGN KEY does it for you.

I don't think it was ever necessary to create an index for a PRIMARY
KEY, the declaration of that constraint automatically creates a unique
index. But the designer of the database may not have known this.

I don't think it hurts anything to have these redundant indexes, but it
uses space that you don't need to use, and may have effects on the query
optimizer. You should be able to drop the redundant indexes safely:

ALTER TABLE DROP INDEX uno, DROP INDEX uno_2;

Regards,
Bill K.

Posted by ancelotp on August 4, 2006, 11:21 am
Please log in for more thread options
hi
thanks so much for that man
i new that code was fishy


Bill Karwin wrote:
> ancelotp@gmail.com wrote:
> > i totally cant understand the last two lines of code in this create
> > statement :
> > UNIQUE uno (uno),
> > KEY uno_2 (uno)
>
> "KEY" is a synonym for "INDEX". It just means that the column `uno` is
> indexed.
>
> "UNIQUE" is a unique constraint; all values in the column must be
> distinct. This also implies the creation of an index on the `uno` column.
>
> It appears in this case that both of these are redundant and
> unnecessary, since you already have the `uno` column declared as a
> PRIMARY KEY, which implies both an index and a unique constraint.
>
> But in some older versions of MySQL, you had to explicitly create an
> index on a column before declaring a foreign key constraint on that
> column. In more recent versions, declaring a FOREIGN KEY does it for you.
>
> I don't think it was ever necessary to create an index for a PRIMARY
> KEY, the declaration of that constraint automatically creates a unique
> index. But the designer of the database may not have known this.
>
> I don't think it hurts anything to have these redundant indexes, but it
> uses space that you don't need to use, and may have effects on the query
> optimizer. You should be able to drop the redundant indexes safely:
>
> ALTER TABLE DROP INDEX uno, DROP INDEX uno_2;
>
> Regards,
> Bill K.


Posted by ancelotp on August 5, 2006, 10:37 am
Please log in for more thread options

ancelotp@gmail.com wrote:
> hi
> thanks so much for that man
> i new that code was fishy
>
>
> Bill Karwin wrote:
> > ancelotp@gmail.com wrote:
> > > i totally cant understand the last two lines of code in this create
> > > statement :
> > > UNIQUE uno (uno),
> > > KEY uno_2 (uno)
> >
> > "KEY" is a synonym for "INDEX". It just means that the column `uno` is
> > indexed.
> >
> > "UNIQUE" is a unique constraint; all values in the column must be
> > distinct. This also implies the creation of an index on the `uno` column.
> >
> > It appears in this case that both of these are redundant and
> > unnecessary, since you already have the `uno` column declared as a
> > PRIMARY KEY, which implies both an index and a unique constraint.
> >
> > But in some older versions of MySQL, you had to explicitly create an
> > index on a column before declaring a foreign key constraint on that
> > column. In more recent versions, declaring a FOREIGN KEY does it for you.
> >
> > I don't think it was ever necessary to create an index for a PRIMARY
> > KEY, the declaration of that constraint automatically creates a unique
> > index. But the designer of the database may not have known this.
> >
> > I don't think it hurts anything to have these redundant indexes, but it
> > uses space that you don't need to use, and may have effects on the query
> > optimizer. You should be able to drop the redundant indexes safely:
> >
> > ALTER TABLE DROP INDEX uno, DROP INDEX uno_2;
> >
> > Regards,
> > Bill K.

hi
i still dont get what the uno_2 is
is it the name for the index variable as different from the variable or
is it some sql convention


Posted by Bill Karwin on August 5, 2006, 2:46 pm
Please log in for more thread options
ancelotp@gmail.com wrote:
> i still dont get what the uno_2 is
> is it the name for the index variable as different from the variable or
> is it some sql convention

Indexes have names, so you can specify the index when you want to drop it.

Sometimes indexes are automatically assigned a name. For instance, in
MySQL the index for a primary key is always named simply "PRIMARY".

Regards,
Bill K.

Similar ThreadsPosted
Can anyone help a newbie figure out MySQL? September 12, 2006, 12:08 pm
A "Commands out of sync;" error October 16, 2006, 3:38 pm
Stored procedure won't allow multiple commands August 16, 2006, 6:10 pm
2014 Commands out of sync; you can't run this command now July 25, 2007, 1:09 pm
After a syntax error: I get thid odd Prompt and can't enter any more commands. March 29, 2006, 6:29 pm
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' January 12, 2006, 9:26 pm
Problem with accent Import to MySQL/Linux from MySQL Windows September 4, 2005, 1:54 am
MySQL variables, modifying Microsoft SQL sequence insert for use in mysql October 7, 2005, 10:54 am
I want to make transfer data between MySQL Server to MySQL Local . February 9, 2006, 1:25 am
restoring a 4.0.17 mysql server without a mysql dump file July 20, 2005, 12:12 pm

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap