How to Define a Composite Primary Key in Sql

How can I define a composite primary key in SQL?

Just for clarification: a table can have at most one primary key. A primary key consists of one or more columns (from that table). If a primary key consists of two or more columns it is called a composite primary key. It is defined as follows:

CREATE TABLE voting (
  QuestionID NUMERIC,
  MemberID NUMERIC,
  PRIMARY KEY (QuestionID, MemberID)
);

The pair (QuestionID,MemberID) must then be unique for the table and neither value can be NULL. If you do a query like this:

SELECT * FROM voting WHERE QuestionID = 7

it will use the primary key's index. If however you do this:

SELECT * FROM voting WHERE MemberID = 7

it won't because to use a composite index requires using all the keys from the "left". If an index is on fields (A,B,C) and your criteria is on B and C then that index is of no use to you for that query. So choose from (QuestionID,MemberID) and (MemberID,QuestionID) whichever is most appropriate for how you will use the table.

If necessary, add an index on the other:

CREATE UNIQUE INDEX idx1 ON voting (MemberID, QuestionID);

How should I define a composite primary key for an existing database table?

Your configuration works fine with EF6. But you must be careful when using Data Annotations with EF Core (looks like Fluent API will be the preferred approach there).

The Keys(primary) section of the documentation explicitly states:

You can also use the Fluent API to configure multiple properties to be the key of an entity (known as a composite key). Composite keys can only be configured using the Fluent API - conventions will never setup a composite key and you can not use Data Annotations to configure one.

So you really need to use:

modelBuilder.Entity().HasKey(e => new { e.OrderNo, e.OrderLineNo });

How to reference a composite primary key in SQL

We declare an SQL FK (FOREIGN KEY) constraint to say that a subrow value for a list of columns always appears elsewhere as a subrow value for a list of columns that forms an SQL PK (PRIMARY KEY) or UNIQUE NOT NULL. Declare it whenever it isn't already implied by other declarations. It must reference the column list in a declared SQL PK (PRIMARY KEY) or UNIQUE NOT NULL. So you must declare that in the referenced table, even if that's already implied by NOT NULLs and a smaller contained PK or UNIQUE NOT NULL.

So note that an SQL PK is not necessarily a PK in the relational sense of being unique but not containing a smaller unique column set, ie being a superkey not containing a smaller superkey, ie being a minimal/irreducible superkey, ie being a CK (candidate key).

Here, you might need to replace the buildingno & roomno FKs by one, (buildingno, roomno) to Room:

CONSTRAINT SESSION_FK12
    FOREIGN KEY(BUILDINGNO,ROOMNO) REFERENCES ROOM(BUILDINGNO,ROOMNO)

That might be appropriate for the meanings of your tables--which in fact you don't give, so we can't know, we can only guess. Eg if buildingno could also be declared PK or UNIQUE NOT NULL in Room, which when roomno IS NOT NULL is actually consistent with and implies (buildingno, roomno) could be declared PK or UNIQUE NOT NULL, maybe your FK is right but your Room declarations are inadequate.

When a subrow value for a list of columns always appears elsewhere as a subrow value for a list of columns that is called an IND (inclusion dependency) constraint. There's no way to declare a non-FK IND in SQL; we must enforce by triggers. That also might be what you need for your design.

You could keep the FK from buildingno to Building, but it's implied by the FK I suggest and the FK in buildingno on Room referencing Building.

referencing part of the composite primary key

Best way to define composite primary key

There is no "best" way, although a lot of people will try to tell you there is.

You can use an artificial key, like your id field, which is usually an integer, or a natural key, which would be your name field.

Which one you choose is mostly personal preference. There are pros and cons to each, but successful applications have been built with both. Do a search for "artificial vs natural key" to find countless debates about which is best.

However, there would be no point to creating an ID field, and then making a composite primary key - use either the ID field alone, and create a unique index on the name, or use the name field and don't bother with an id.

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)

Two Composite Primary Key in SQL

 Name VARCHAR(20) NOT NULL,
 UserId int NOT NULL,
 GroupId int NOT NULL,
 UNIQUE ( UserId, Name),
 UNIQUE ( GroupId, Name)

In the relational model and in SQL there is no logical difference between one key and another so there's no very strong reason to have a different syntax for specifying one key over any other. However, for better or worse, the authors of the SQL standard decided to make a limitation that the PRIMARY KEY constraint syntax can only be used once per table and that where you need more than one key you have to use one or more UNIQUE constraints instead. Arguably it would be desirable to drop that limitation but since it's fundamentally just a bit of syntactical sugar that's unlikely to happen any time soon.

Why Composite Primary key when I can use Single Primary key with Unique constraints on composite columns?

You do not need a primary key to enforce uniqueness. You can use a unique constraint or index instead.

I am not a fan of composite primary keys. Here are some reasons:

• All foreign key references have to include all the keys in the correct order and matching types. This makes is slightly more cumbersome to define those tables.
• Because the composite keys are included in all referencing tables, those tables are often larger, which results in worse performance.
• If you decide that you want to change the type of one of the component keys -- say the length of a string or an int to a numeric -- you have to modify lots and lots of tables.
• When joining tables, you have to include all the keys. If you miss one . . . well, the code is syntactically correct but the results are wrong.

There are occasions where composite keys are acceptable, such as tables that have no foreign key references. Even in those cases, I use synthetic keys, but I totally understand the other perspective.

Create a one to many relationship between 2 tables which have composite primary keys

In Access the process to set up a 1 to many relationship between tables with composite keys is drag and drop just like with normal primary keys, but there is a pitfall. Consider tables 3 & 4 both with composite primary keys.

Note Table 4 has 2 foreign key columns (A and B) in addition to the composite primary key (C and D).

directions for creating a composite primary key: https://www.techwalla.com/articles/creating-composite-keys-tutorial-for-ms-access

To create a composite primary key, remove any current primary key then use shift-click to select the fields for your composite key. The pitfall is you must use primary key in the ribbon rather than right click to set your fields as primary keys. Both A and B will be marked with the key symbol when you are done.

for the last step go back to the relationships tool shift-click columns A & B in Table 3 and drag them to Table 4 and release. Then select the usual options and you are done.

Composite primary key

I personally find composite primary keys to be painful. For every table that you wish to join to your "sources" table you will need to add both the source_id and id_on_source field.

I would create a standard auto-incrementing primary key on your sources table and add a unique index on source_id and id_on_source columns.

This then allows you to add just the id of the sources table as a foreign key on other tables.

Generally I have also found support for composite primary keys within many frameworks and tooling products to be "patchy" at best and non-existent in others

---

Related Topics