UNIQUE field

I just have a easy question.. I hope.
I would just like to know how would you make a field in a tables unique. This field I am refering to is not the primary key but a field like USERNAME that can only have one entry in a database. Thank you in advance....

vncmrc
LVL 3
vncmrc12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nesnemisCommented:
Hi vncmrc12,

like this for instance:
create table
      testuniquemultnulls
     ( id smallint  auto_increment
     , col1  varchar(3)  not null
     , col2  varchar(3)  null
     , primary key (id)
     , unique (col2)
     );

nesnemis
0
nesnemisCommented:
vncmrc12,
 you can also read more at:
    http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
0
nicholassolutionsCommented:
simple:

In your table creation statement:

CREAT TABLE table_name(
column_name1 column_type PRIMARY KEY NOT NULL DEFAULT '0' AUTO_INCREMENT,
    .
    .
    .
column_name2 column_type UNIQUE
);


or if the table already exists:

ALTER TABLE table_name ADD UNIQUE (column_name);

to add a unique column to an existing table:

ALTER TABLE table_name ADD COLUMN column_name column_type;
ALTER TABLE table_name ADD UNIQUE (column_name);


Hope that helps,
Matt
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

vncmrc12Author Commented:


Hi nicholassolutions , did the "ALTER TABLE table_name ADD UNIQUE (column_name);" but this is the error?.. thank you!!!

------------- ERROR -------------------
BLOB column 'docPractice' used in key specification without a key length
0
nesnemisCommented:
how does your script look like?
0
nicholassolutionsCommented:
Did you create the column in the line before? I'm guessing maybe you did this?

ALTER TABLE table_name ADD COLUMN docPractice BLOB;
ALTER TABLE table_name ADD UNIQUE (docPractice);

You need to specify the length of docPractice...so do something like this:

ALTER TABLE table_name ADD COLUMN docPractice BLOB(60000);
ALTER TABLE table_name ADD UNIQUE (docPractice);

-Matt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vncmrc12Author Commented:
Got that to work... thanks guys
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.