How do I create Constraint Expressions for MSSQL 2005?

I am running MSSQL Server 2005 Standard Edition on Windows Server 2003 Enterprise Edition.
I am using the SQL Server Management Studio included witth the MSSQL 2005 Standard installation running on the Database Computer and using the login credentials from the Currently Logged In Enterprise Admins Account.

I open up a table from the database Tree and select to modify it.
Right clicking on a row will allow me to edit constraint expressions.
I have previously use expressions like ComumnX < 8 but i am having trouble with unique expressions.

How do I create an expression that checks for duplicates in a single column, how do I create an Expression that checks for duplicate combinations of a couple rows ie. no 2 rows can have the same first AND last name, and where can I find more information on how to create the expressions?
LVL 5
Titanium_SniperAsked:
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.

 
Aneesh RetnakaranDatabase AdministratorCommented:
>How do I create an expression that checks for duplicates in a single column
just create a unique key constraint on that column
> how do I create an Expression that checks for duplicate combinations of a couple rows ie. no 2 rows can have the same first AND last name
again the same, create unique constraints
0
 
Patrick MatthewsCommented:
Titanium_Sniper said:
>>How do I create an expression that checks for duplicates in a single column, how do I
>>create an Expression that checks for duplicate combinations of a couple rows ie. no 2
>>rows can have the same first AND last name,

Create a unique index on the two columns.

>>and where can I find more information on how to create the expressions?

Books Online will tell you in great detail.
0
 
Titanium_SniperAuthor Commented:
Sory, i have been looking online and have tried:

tablename UNIQUE NONCLUSTERED
UNIQUE NONCLUSTERED tablename
dbo.tablename UNIQUE NONCLUSTERED
UNIQUE NONCLUSTERED dbo.tablename
databasename.tablename UNIQUE NONCLUSTERED
UNIQUE NONCLUSTERED databasename.tablename
UNIQUE NONCLUSTEREDtablename
UNIQUE NONCLUSTEREDdbo.tablename
UNIQUE NONCLUSTEREDdatabasename.tablename
and many more

i am having trouble formatting it, every time i try the program says it does not understand the expression and to fix it.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE TABLE testTable ( column_a INT, COLUMN_b VARCHAR(10))
GO
--unique constraint on col b alone
ALTER TABLE testTable WITH NOCHECK
ADD CONSTRAINT uni_B UNIQUE (Column_B)
GO
--constraint on both a and b
ALTER TABLE testTable WITH NOCHECK
ADD CONSTRAINT uni_BothAandB UNIQUE (Column_a,Column_b)
0

Experts Exchange Solution brought to you by ConnectWise

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
 
Titanium_SniperAuthor Commented:
Thanks a ton, I didn't use a space.
0
 
Titanium_SniperAuthor Commented:
I added it, and it worked for a while,and even threw the correct exception in my program when I tried to add duplicates, but now it does not work anymore and it does not show up in the constraints page, and I cannot add it again.

i ran the command "DELETE FROM TABLENAME" Could that have removed the constraint?

All it asks for is the constraint expression, all other properties like the name or description are in little property boxed in the window where you edit the constraint expressions.

I tried to add the expression: "UNIQUE (Product_Number)" where Product_Number is the name of the column.
0
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.

All Courses

From novice to tech pro — start learning today.