How to Create Composite Primary Key in SQL Server 2008

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)

Composite primary key sql relationship

If you have a composite primary key, then all foreign key constraints that reference it must use all columns of the composite primary key.

So in your case - TableTwo must reference TableOne(ReportID, Date)

The only way around this would be to create a UNIQUE INDEX on TableOne (ReportID) - then you can create a foreign key reference to that unique constraint alone.

But that then begs the question: why isn't ReportID alone the primary key, if it's already unique (since you can put a UNIQUE INDEX on it) and NOT NULL (since it's part of the compound PK) ?

Step 1: create the UNIQUE INDEX:

CREATE UNIQUE NONCLUSTERED INDEX UIX_TableOne ON dbo.TableOne(ReportID);

Step 2: create the foreign key from TableTwo:

ALTER TABLE dbo.TableTwo
ADD CONSTRAINT FK_TableTwo_TableOne
FOREIGN KEY(ReportID) REFERENCES dbo.TableOne(ReportID)

How do you create a composite key for a View in SQL Server 2008?

You could recreate the view as a table, add a primary key, import it into EF, and then drop table and reinstate the view. This will get your table into EF, but then you have to be careful whenever you update the model again in the future.

Alternatively, you could create a model db, with all the same object names as your actual db, but with views created as tables. At design time, use the model db, and at run time, use the actual db. So long as the view is updateable, the EF runtime won't care that it's actually a view, and not a table.

If you are feeling compulsive, and have a lot a tables, it wouldn't be too hard to auto-magically generate the model db from the actual db.

Sql Server Composite Primary Key

I believe I have found the cause and IMO this is a very poor design decision by the Microsoft folks.

I went to create a new Foreign Key and set the Primary Key Table and Foreign Key Table and their columns. I then realized that I needed to clean the data in the table before creating the Foreign Key, so I clicked close. What I discovered is that this creates a pending Foreign Key as I described in my question, a FK comprised of the Primary Key fields referencing itself.

So what I can reasonably guess has happened is that at some point on these 5 tables, I started to create a FK, thought I had aborted that operation when I hit close, and then made another change to the table, such as adding a field or changing a datatype, and then when I saved the table, I got an unintended FK created. I might add that it was an FK with parameters I had never set.

This is a bug in my opinion.

Edit: I just discovered that it's actually worse than I described. Even if you hit the cancel button on the Foreign Key creation dialog, it still creates the self-referencing Foreign Key in a pending state. This is terribly stupid.

Edit 2: This keeps getting worse the more I investigate. I just got it to save one of these self-referencing FK's by closing SSMS and clicking No on the save changes dialog. I reopened SSMS and the self-referencing FK had been created anyway. Just to recap, I clicked Cancel on the create FK dialog, and No on the save changes dialog, and it created a Foreign Key with parameters I had never set.

Compound primary key in Table type variable

You can define a composite primary key like this:

DECLARE @MyTable TABLE
(   
    PersonID INT NOT NULL,    
    Person2ID INT NOT NULL,    
    Description NVARCHAR(100),
    PRIMARY KEY (PersonID, Person2ID)
);

SQL 2008 Composite Primary Key With One Part of the Key Auto Increment

If you're not doing a lot of inserts to the table you can probably remove the identity column and simply do:

IF NOT EXISTS ( SELECT * FROM table WHERE PK1 = 2 AND strValue = '3b' )
BEGIN
  DECLARE @next_PK2 INT

  SELECT
    @next_PK2 = ISNULL(MAX(PK2) + 1, 0) + 1
  FROM
    table
  WHERE
    PK1 = 2

  INSERT table VALUES (2, @next_PK2, '3b')
END

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.

---

Related Topics