SQL server 2008: Create a Candidate key


How do I create a candidate key in sql server 2008?
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
a candidate key is basically used in designing context.for example, the SSN might be a candidate key for the employees table, as that field is unique, normally.still, in your table design, you might not want to use that value as foreign key, or you might want to create employee records without knowing the SSN from the beginning on, so you want to have another, virtual value (seqence/auto_increment/identity/ ...) to fullfill the primary key role.the SSN, then, would be a candidate key.
AWestEngAuthor Commented:
what I want to do is protect inserts of new data if the serialnumber is the same and the revision number is the same, so I need a unique key that conists of [Serialnumber] and [RevisionNumber]
AWestEngAuthor Commented:
I need to have this to secure the table inserts regarding some FDA rules
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, you create a unique constraint with those 2 columns.

note: 1 constraint for both columns, not 2 constraints with 1 column each....
Guy Hengel [angelIII / a3]Billing EngineerCommented:
AWestEngAuthor Commented:
ok, but I dont' know how to make two columns unique, the "key" can only be placed on ome column.
AWestEngAuthor Commented:
I found this.
1> "Mange check constraints"
2> Add a new constraint
3> ?? How do I create the expression ??
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the "KEY" is the primary key stuff, which CAN be put over several column also.

check the above link, in the ALTER TABLE you can find the syntax to add a unique constraint ...
ALTER TABLE yourtable ADD CONSTRAINT c_unique_yourtable UNIQUE ( col1, col2 )

Open in new window

AWestEngAuthor Commented:
ok, but can I do it iwith the manger too? or must I use alter table?
AWestEngAuthor Commented:
ok, so the Primary key can be put on two columns too? Isn't that better then?
How do I do that
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in the manager, you can use:
> add constraint
> add index (make it unique, and add the 2 columns)

Guy Hengel [angelIII / a3]Billing EngineerCommented:
check out this youtube video:
AWestEngAuthor Commented:
ok thx , but you said something about setting two coulmuns to be a Primary key, or did I understand you wrong?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that would be possible, yes.
in the interface, ensure there are not columns marked with the key.
then, select both columns (which are rows in the interface).

then, click the key symbol, it should be added to both columns.
then, save the table ...

Experts Exchange Solution brought to you by

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
AWestEngAuthor Commented:
thx man :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.