Set a field to be unique

[lilboi]

Hi guys!

I'm using SQL Enterprise Manager, but could not find any option when designing a table to set a field to require to be unique.

Table: Users
Fields: ID, Login, Password, FrstName, LastName

ID = Unique ID given to account on creation.
Login & Password, they will create by themselves.

Now, how can I set Login to require to be unique from the others?

As well...I set the Password field as varchar. Is there an option that I can choose so that all the passwords appear as ***** in sql?

Thanks a bunch guys!

 

[vbkris]

hi,

you will find that field in Index / Keys tab of the "Manage Relationships" button.

Create a new index, select the field, check the "Create Unique" checkbox.

thats it...

Known is handfull, Unknown is worldfull

 

[bborissov]

If this field is Integer you could set it to be IDENTITY field. That will guarantee you that the field will be unique. BUT you can't edit it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[lilboi]

thank you for your inputs guys.

I tried out what vbkris said but it gave me an error.

Here's what I did.

1) Index / Keys tab of the "Manage Relationships" button.
2) Create a new index
3) Selected "login"
4) Checked "Create Unique"
btw: Index Filegroup = PRIMARY (i don't know what this means but I already had "ID" as primary)

I got this error:

Agentlist' table
- Unable to create index 'IX_Agentlist'.  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 84. Most significant primary key is '<NULL>'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

I checked and all the entries for the field are <NULL>
I'm not sure what they mean by index ID 84 tho. Do you have any idea on what's causing this?

Table Agentlist is related to Table QueueDetail
both "login" i called "WebID" have all the fields set to <NULL>

Thanks!

 

[alicorn2]

If the entries are already null, then they're not unique. You can create the index with NO CHECK (remove the checkmark from "Check existing data on creation") which means "from this point on, make sure it's unique" but it's probably better if you fill in the data first. You should also disallow NULLS, probably, because it will allow one and only one NULL ... that's unique, after all