How to Make a Composite Key with SQL Server Management Studio

Creating composite primary key in SQL Server

If you use management studio, simply select the wardNo, BHTNo, testID columns and click on the key mark in the toolbar.

Command for this is,

ALTER TABLE dbo.testRequest
ADD CONSTRAINT PK_TestRequest 
PRIMARY KEY (wardNo, BHTNo, TestID)

How to create composite foreign key in sql server management studio 2012

In Object Explorer, go to your table and select Keys > New Foreign Key from the context menu:

From the dialog box that pops up, click on the Add button to create a new foreign key:

Give it a meaningful name and then click on the ... button to open the Tables and Columns specification dialog box:

Fill in the necessary columns for the parent and the child tables, click OK and you're done!

Or much easier and more efficiently - use a T-SQL script!

ALTER TABLE dbo.OtherTable
ADD CONSTRAINT FK_OtherTable_ParentTable
FOREIGN KEY(OrderId, CompanyId) REFERENCES dbo.ParentTable(OrderId, CompanyId)

SQL Server Management Studio - Composite primary keys

As SQL Server does not allow a function based index, one solution is to force inserting the values such that the "smaller" one is always stored in the first column:

alter table myTable add constraint chk_pk 
   check (col1_1 <= col_2);

If you don't like this (because you don't want to force a special "ordering" of the values during insert), you need to define two computed columns and create a unique index on them:

ALTER TABLE myTable ADD 
    min_pk AS case when col_1 < col_2 then col_1 else col_2 end,
    max_pk AS case when col_1 > col_2 then col_1 else col_2 end;

CREATE UNIQUE INDEX idx_unique_pk ON myTable (min_pk, max_pk);

Using Composite Keys in Microsoft SQL Server

To enforce uniqueness on 2 columns, you can add a unique constraint like this:

ALTER TABLE dbo.MilestoneCategory
ADD CONSTRAINT constraint_name 
UNIQUE NONCLUSTERED (Name,Type);

PS: I think you should have only one primary key, which is Item in your example records. You could add MilestoneCategoryID as an int, identity column.

See this answer for more details on unique constraints.

How to make a composite key without making it a primary key in SQL Server ?

What do you mean by making them composite key ?

Do you want to make them unique (in combination)?

Just create a UNIQUE CONSTRAINT on them:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT UC_YourTable_Col1_Col2
UNIQUE(Col1, Col2)

Or what else do you want to achieve by making them composite key ?

SQL Server - Composite key creation

Unfortunately, SQL Server Compact Edition does not support clustered indexes. This goes for the Primary Key as well.

Link showing that it does not support clustered indexes:

- http://technet.microsoft.com/en-us/library/ms345331(v=sql.105).aspx

Link showing that PRIMARY KEYs are maintained by unique indexes:

- http://technet.microsoft.com/en-us/library/ms173393.aspx

Creating a composite foreign key in SQL Server 2008

Some of this is focused, some of this is context for others having any sort of problem like this (like anyone actually searches first?)

The first thing to check when you have a problem creating a key is make sure you did not mismatch the data types in the two tables. If you have an bigint in one and an int in the other, it will blow. This is true on all keys, but more likely to crop up if you use multiple fields. Simple math shows the reason why the chance increases.

The next issue is data. If you cannot create the key due to data, you have to find out what exists in the child table that does not exist in the parent table. LEFT JOIN the tables (secondary on the second/left side of the join) and only include rows where the primary table is null. You will either have to create these records in the parent table or get rid of them.

One way "around" this is set up a new primary key on the parent table. You then create a foreign key on this new primary key and match as many records as you can in the child table. You then have the join set up and you can go about cleaning as a secondary operation.

Which is better? New primary key or working with the composite key? This really depends on the nature of the data, but I am more fond of using a derived key over a natural key or a composite key. But, there are times where the work necessary to get a single field derived key is a lot of work.

How to make a composite key out of two foreign keys

No, it doesn't make sense to assemble a single composite FK. Those are two separate foreign key constraints, unrelated to each other.

Your table is perfectly correct as:

CREATE TABLE Table3(
  pizza varchar(12),
  ingredient varchar(12),
  amount int,
  CONSTRAINT FK_pizzaRecipe FOREIGN KEY (pizza)
  REFERENCES Table1(pizza),
  CONSTRAINT FK_ingredientBase FOREIGN KEY (ingredient)
  REFERENCES Table2(ingredient)
);

How to set (combine) two primary keys in a table

The easiest way would be to use a T-SQL command in SSMS Express rather than trying to use the visual designers.....

Once you've designed and created your table, try something like this:

ALTER TABLE dbo.YourTableNameHere
ADD CONSTRAINT PK_YourTableNameHere
PRIMARY KEY(Item_Id, Purchase_Id)

---

Related Topics