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

Why did you set `Mysql` and `sql-server` tags? Do you use both RDBMS? — Roman Marusyk, Dec 14 '15 at 13:23
Your table is pretty small. Could you script it so we can see the definition of the keys? — Simon Wilson, Dec 14 '15 at 13:23
What is the value for the field Type in your second example? — Steve, Dec 14 '15 at 13:24
Made a couple mistakes, have corrected them. The table is designed in SQL Management Studio, is there a way I can show code from that which would be of use? — Snicklefritz, Dec 14 '15 at 13:26
You have this flagged as `c#`, is this a problem when running the inserts from C# or can you reproduce the problem with SQL inserts only? — gmiley, Dec 14 '15 at 13:26
@Snicklefritz. Right click the table and choose Script Table As...you can do this with most objects in SMS — Simon Wilson, Dec 14 '15 at 13:27
Possible duplicate of [SQL Server 2005 How Create a Unique Constraint?](http://stackoverflow.com/questions/64981/sql-server-2005-how-create-a-unique-constraint) — Jess, Dec 14 '15 at 13:32
Can you write exactly the error message? Are you sure that you have a Name in your Type related table with the value equal to SYS or PRE? — Steve, Dec 14 '15 at 13:50
Whether I save changes to the server through c# code or directly insert items I get the same error which is: Violation of UNIQUE KEY constraint 'PK_MilestoneCategory'. Cannot insert duplicate key in object 'dbo.MilestoneCategory'. The duplicate key value is (MILE50, SYS). — Snicklefritz, Dec 14 '15 at 13:54
run a query and select everything in your table. verify that the value you are inserting does not exist already. — Jeremy, Dec 14 '15 at 13:58
and check also if you are looking at the correct database. In particular verify the connection string used by your application against the database used by SSMS. — Steve, Dec 14 '15 at 14:04
@Jeremy I've edited my question to show that the item does not already exist in the table — Snicklefritz, Dec 15 '15 at 12:33
@Snicklefritz umm. you have a where clause looking for one thing, and it finds the one thing, so you can't re-insert it. — Jeremy, Dec 15 '15 at 12:47
@Jeremy I'm not looking to insert that item though, when I insert Item 2 (see example in question) I get a duplicate key violation. The type properties are different. — Snicklefritz, Dec 15 '15 at 13:02
@Snicklefritz your select statement will only show me one record in the table because of the where clause. What are the results when you remove the where clause? — Jeremy, Dec 15 '15 at 13:04
@Jeremy There's a lot of results but there is still just one item with the name 'MILE50', the Type property is 'PRE' — Snicklefritz, Dec 15 '15 at 13:14
@Jeremy It's not there. I'll do some further tests and then come back. Thanks a lot for your help. — Snicklefritz, Dec 15 '15 at 13:20
The problem turned out to be a bit of a rabbit hole but I think it's fair to put @Jess 's answer down as the accepted answer. Thanks everyone for your help. — Snicklefritz, Dec 17 '15 at 09:17

score 3 · Accepted Answer · edited May 23 '17 at 11:52

3

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.

edited May 23 '17 at 11:52

Community

1
1

answered Dec 14 '15 at 13:30

Jess

23,901
21
124
145

1

Thanks very much for your help, after clearing out of the database of rogue items this is what ultimately solved my problem. It doesn't behave quite as I'd like but the majority is there and I have a much better understanding of SQL as a result. – Snicklefritz Dec 17 '15 at 09:13

Using Composite Keys in Microsoft SQL Server

1 Answers1