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

x
?
Solved

How do I create Constraint Expressions for MSSQL 2005?

Posted on 2007-08-04
6
Medium Priority
?
7,732 Views
Last Modified: 2013-11-05
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?
0
Comment
Question by:Titanium_Sniper
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19631061
>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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19631064
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
 
LVL 5

Author Comment

by:Titanium_Sniper
ID: 19631079
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 200 total points
ID: 19631108
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
 
LVL 5

Author Comment

by:Titanium_Sniper
ID: 19631467
Thanks a ton, I didn't use a space.
0
 
LVL 5

Author Comment

by:Titanium_Sniper
ID: 19632343
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

830 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