Using compound keys in Microsoft SQL Server

Question

I have an SQL table with the following structure:

I intend this table to work in such a way that the element entered into the table is only a duplicate if it has the same name and type, so that the following example is valid if these two elements were added to 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 the data as shown, this leads to an error stating that there has been a violation of the primary key constraint. Didn't I understand how Composite Keys work in SQL? If so, how can I achieve what I am looking for?

Thank you 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 

Running 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 ensure uniqueness in the two columns, you can add a unique constraint similar to 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 examples. You can add MilestoneCategoryID as an int, identity column .

See this answer for more unique restrictions .