178

How do I make a composite key with SQL Server Management Studio?

I want two INT columns to form the identity (unique) for a table

zx8754
  • 52,746
  • 12
  • 114
  • 209
mrblah
  • 99,669
  • 140
  • 310
  • 420

7 Answers7

372

enter image description here

  1. Open the design table tab
  2. Highlight your two INT fields (Ctrl/Shift+click on the grey blocks in the very first column)
  3. Right click -> Set primary key
Cory
  • 12,404
  • 7
  • 33
  • 28
  • 5
    To highlight multiple fields, click the blocks in front of the columns "column name" and "data type" and such. Ctrl/Shift+Clicking on the column name fields won't actually do anything. – Pimgd Oct 10 '13 at 14:50
  • 5
    So if one is a string and the other is an int, it's not possible? Doesn't seem to be... – B. Clay Shannon-B. Crow Raven Nov 19 '13 at 23:06
  • 4
    I would downvote this but then I realised the OP specifically asked for how to do it in SSMS. You should always script database changes + if you use tools like this, you'll never learn how to do the changes for yourself – JonnyRaa Apr 15 '15 at 15:45
  • 2
    @JonnyLeeds thats a very high horse your on there. Whats wrong with GUI if that's what people prefer? – leinad13 Jun 09 '16 at 09:16
  • 5
    @leinad13 I must have been having a bad day! But the major problem is that they aren't repeatable and it generally means that people are making uncontrolled ad hoc changes and tweaks to fix database/schema problems. That's a very bad sign - they are undocumented (eg by having the code to fix written down and checked into version control and/or having a tool that automatically applies the right changes to whatever the database version is) and if you have more than one installation then you're in serious trouble as soon the databases won't have the same schema – JonnyRaa Jul 27 '16 at 12:11
  • @JonnyRaa Indeed, it is good to script database changes. In SQL Server Management Studio, one can script out databases, database tables, indexes, etc. For example, one can create a table and set up a primary key and an index using the GUI, and then script out the results by right-clicking the table and choosing Script Table as... This tool has helped me learn how to do things in T-SQL that I could previously only do through the SSMS GUI. – theguy Apr 14 '20 at 17:50
70

here is some code to do it:

-- Sample Table
create table myTable 
(
    Column1 int not null,
    Column2 int not null
)
GO

-- Add Constraint
ALTER TABLE myTable
    ADD CONSTRAINT pk_myConstraint PRIMARY KEY (Column1,Column2)
GO

I added the constraint as a separate statement because I presume your table has already been created.

Roatin Marth
  • 23,589
  • 3
  • 51
  • 55
  • I guess this answer fits since you can run this within Query window of SSMS... ;) – dance2die Oct 13 '09 at 00:07
  • 3
    Scripting FTW, and for explicit naming! The external `ADD PRIMARY KEY` and internal `PRIMARY KEY` definitions will give random names to the keys, which can make it a pain the bajingo to figure which belongs where if you don't have the db diagram + tables. – Daevin Aug 25 '15 at 14:55
45
create table my_table (
    id_part1 int not null,
    id_part2 int not null,
    primary key (id_part1, id_part2)
)
yfeldblum
  • 65,165
  • 12
  • 129
  • 169
17

In design mode (right click table select modify) highlight both columns right click and choose set primary key

Gratzy
  • 9,164
  • 4
  • 30
  • 45
8

Open up the table designer in SQL Server Management Studio (right-click table and select 'Design')

Holding down the Ctrl key highlight two or more columns in the left hand table margin

Hit the little 'Key' on the standard menu bar at the top

You're done..

:-)

user4878037
  • 89
  • 1
  • 1
6
create table myTable 
(
    Column1 int not null,
    Column2 int not null
)
GO


ALTER TABLE myTable
    ADD  PRIMARY KEY (Column1,Column2)
GO
VladL
  • 12,769
  • 10
  • 63
  • 83
Tejas
  • 69
  • 1
  • 1
5

Highlight both rows in the table design view and click on the key icon, they will now be a composite primary key.

I'm not sure of your question, but only one column per table may be an IDENTITY column, not both.

KM.
  • 101,727
  • 34
  • 178
  • 212