Newbie - specify column constraints

[MrPeds]

Hi,

I have a very simple table which has the following fields:

ID integer 'primary key
userName varchar 'length 6
displayOrder integer
order integer
areaCode varchar 'length 6

I would like to be able to define a constraint (or an index?) that specifies that the first 2 rows cannot be valid in the table:

ID |..userName | ..areaCode
-----------------------------
1 Fred ABC123
2 Fred ABC123 'can't have duplicate areacode!
3 Fred BBB222

So in a nutshell, no user can have more than one record with the same areaCode.

How do i define this in Enterprise Manager?

THanks for you help in advance!

MrPeds

 

[ClaireHCM]

Create Table tb
(Id int constraint pk_tb primary key,
UserName varchar(6),
DisplayOrder int,
Orders int,
AreaCode varchar(6) constraint u_tb Unique
)

 

[JamesLean]

Claire, that will only make the areacode unique. The way I understand it, MrPeds wants the combination of username and areacode to be unique:

CREATE TABLE #tbl (
  id int CONSTRAINT pk_tbl PRIMARY KEY,
  username varchar(6),
  displayorder int,
  [order] int,
  areacode varchar(6),
  CONSTRAINT unq_tbl UNIQUE (username, areacode)
)

--James

 

[Frank49001]

James, What is the role of CONSTAINT in your code sample?

Thanks,

Frank

 

[ClaireHCM]

James, You are right.

Frank,
Why code CONSTAINT ?
It is for naming convention ,You can also remove the constraint +Constraint_name. But not recommend !

Without specifying "Constraint+Constraint_name",SQL will automatically create a name for your constraint. But it's going to be not legible. And you have to use sp_help 'tablename' in order to get the name of the constraint.

Say you want to define the name of your primary of MyTable
you can code as "constraint pk_MyTable" ,it's more organized than just let SQL to create some crappy name for the primary key

 

[MrPeds]

I used an slightly different solution in the end - I created a unique Index on the Username and Area fields, although creating a COnstraint would also achieve the same result.

I'm not sure which solution would be better or more efficient in the end though!

Thanks for your help!

MrPeds

 

[JamesLean]

FYI, Microsoft recommends using constraints to enforce uniqueness rather than indexes.

(Although a constraint will actually create a unique index to enforce it!)

--James