UNIQUE fields in SQL Server 2005

Question

I am writing a forum for the company I work for. I am in the middle of creating the database tables and I have set up all my primary keys but I want user_name to be unique so no two users can have the same username when registering.

Could someone shed some light on how this can be done, I have a basic level of knowledge when it comes to SQL.

Thanks!

score 0 · Answer 1 · answered Jan 17 '13 at 13:37

0

create unique index. That will force unique values for that column.

CREATE UNIQUE INDEX index_name ON table_name (column_name)

answered Jan 17 '13 at 13:37

Satish

713
1
5
18

I would have beaten you to be first answerer, but the answer is so short I had to lengthen it a few times, and then I had to fill out a captcha! – Kind Contributor Jan 17 '13 at 13:38
I have seen, while I have been creating them in SQL server management studio that you can do it by right-clicking on the field and going to indexes/keys I think it is but then I don't know the syntax to enter to make the field unique. – CodingNewbie Jan 17 '13 at 13:43

score 0 · Accepted Answer · edited May 23 '17 at 10:34

0

Create a unique Index on user_name.

In SQL Management Studio:

right click the Table in question, left click Design
Right click on some white-space (or the junction of the design table), left click Indexes/Keys
Click the Add button to create a new index
Make sure the new one is selected in the list on the left
Click the Triple dot button in the value area for the columns property of the index
Select user_name from the drop-down box
Click OK
Set Is Unique to True
Click Close
Press Ctrl+S to save (will run the appropriate background scripts)

Update:

From SQL Server 2005 How Create a Unique Constraint?

In SQL Server Management Studio Express:

Right-click table, choose Modify

Right-click field, choose Indexes/Keys...

Click Add

For Columns, select the field name you want to be unique.

For Type, choose Unique Key.

Click Close, Save the table.

edited May 23 '17 at 10:34

Community

1
1

answered Jan 17 '13 at 13:37

Kind Contributor

17,547
6
53
70

I'm hedging my bets on GUI instructions... :) – Kind Contributor Jan 17 '13 at 13:45
I can only open table or modify. If I right-click the User_name field and go to indexes/keys, the only entry in there is my PK. So do I need to add a new one and select user_name? – CodingNewbie Jan 17 '13 at 13:57
Do you happen to know a different way as I don't get an option called 'Is Unique'is there a different way to do this? – CodingNewbie Jan 17 '13 at 14:53
Satish's answer may be more applicable in such a case then, DDL. I'm not sure why 'Is Unique' is missing, what version/edition of SQL Server / SQL Management studio are you running? – Kind Contributor Jan 18 '13 at 05:16
I am using SQL Server 2005 and therefore can only assume that it's also SQL Management Studio 2005 too. – CodingNewbie Jan 18 '13 at 09:28
Any luck with trying the "SQL Server 2005 How Create a Unique Constraint" steps above? – Kind Contributor Jan 20 '13 at 01:26
Yes I have managed to sort it out, thank you ever so much for everyones help. I know your probably all pretty frustrated at how easy this was. – CodingNewbie Jan 22 '13 at 10:00
Care to tick my answer as the answer? – Kind Contributor Jan 23 '13 at 04:12
I do apologise mate, I wasn't in work last week from the 23rd. Your answer was correct and it's now working great! – CodingNewbie Jan 28 '13 at 08:59

UNIQUE fields in SQL Server 2005

2 Answers2