[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

UNIQUE field

Posted on 2004-11-09
7
Medium Priority
?
1,183 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:vncmrc12
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Expert Comment

by:nesnemis
ID: 12531381
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
 
LVL 3

Expert Comment

by:nesnemis
ID: 12531393
vncmrc12,
 you can also read more at:
    http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
0
 
LVL 15

Expert Comment

by:nicholassolutions
ID: 12531432
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 3

Author Comment

by:vncmrc12
ID: 12532397


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
 
LVL 3

Expert Comment

by:nesnemis
ID: 12533050
how does your script look like?
0
 
LVL 15

Accepted Solution

by:
nicholassolutions earned 400 total points
ID: 12536837
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
 
LVL 3

Author Comment

by:vncmrc12
ID: 12539075
Got that to work... thanks guys
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question