Using Composite Keys in Microsoft SQL Server

Question

I have a SQL table with the following structure:

I intend this table to work in such a way that an item entered in the table is only a duplicate if it has the same Name and Type so that the following example would be valid if these two items were added to the the database.

Item 1:
Name: MILE50
Acronym: MS50
Type: PRE
Color: white

---

Item 2:
Name: MILE50
Acronym: MS50
Type: SYS
Color: white

---

Currently, if I enter data as shown it results in an error stating that there has been a violation of the Primary Key constraint. Have I misunderstood how Composite Keys work in SQL? If so, how could I achieve what I'm looking for?

Thanks very much.

EDIT: Updated SQL script

    USE [ProjectPlannerDatabase]
GO

/****** Object:  Table [dbo].[MilestoneCategory]    Script Date: 14/12/2015 14:55:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MilestoneCategory](
    [Name] [varchar](200) NOT NULL,
    [Acronym] [varchar](200) NOT NULL,
    [Type] [varchar](20) NOT NULL,
    [Color] [varchar](200) NOT NULL,
 CONSTRAINT [PK_MilestoneCategory] UNIQUE NONCLUSTERED 
(
    [Name] ASC,
    [Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MilestoneCategory]  WITH CHECK ADD  CONSTRAINT [FK_MilestoneCategory_MilestoneClass] FOREIGN KEY([Type])
REFERENCES [dbo].[MilestoneType] ([Name])
GO

ALTER TABLE [dbo].[MilestoneCategory] CHECK CONSTRAINT [FK_MilestoneCategory_MilestoneClass]
GO

Executing the following script gives only one entry:

  Name   Acronym Type Color
1 MILE50 MS50    PRE  white

USE [ProjectPlannerDatabase]
GO

SELECT [Name]
      ,[Acronym]
      ,[Type]
      ,[Color]
  FROM [dbo].[MilestoneCategory]
  WHERE Name='MILE50'
  AND Acronym='MS50'
GO

Answer 1

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.