Composite Primary Key

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);

Composite Primary Keys : Good or Bad?

There is no conclusion that composite primary keys are bad.

The best practice is to have some column or columns that uniquely identify a row. But in some tables a single column is not enough by itself to uniquely identify a row.

SQL (and the relational model) allows a composite primary key. It is a good practice is some cases. Or, another way of looking at it is that it's not a bad practice in all cases.

Some people have the opinion that every table should have an integer column that automatically generates unique values, and that should serve as the primary key. Some people also claim that this primary key column should always be called id. But those are conventions, not necessarily best practices. Conventions have some benefit, because it simplifies certain decisions. But conventions are also restrictive.

You may have an order with multiple payments because some people purchase on layaway, or else they have multiple sources of payment (two credit cards, for instance), or two different people want to pay for a share of the order (I frequently go to a restaurant with a friend, and we each pay for our own meal, so the staff process half of the order on each of our credit cards).

I would design the system you describe as follows:

Products  : product_id (PK)

Orders    : order_id (PK)

LineItems : product_id is (FK) to Products
            order_id is (FK) to Orders
            (product_id, order_id) is (PK)

Payments  : order_id (FK)
            payment_id - ordinal for each order_id
            (order_id, payment_id) is (PK)

This is also related to the concept of identifying relationship. If it's definitional that a payment exists only because an order exist, then make the order part of the primary key.

Note the LineItems table also lacks its own auto-increment, single-column primary key. A many-to-many table is a classic example of a good use of a composite primary key.

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.

Can I create a composite key for a third table from the primary keys of 2 different tables? Does MySQL support it?

You can create composite primary key with foreign keys (primary keys from others tables), you have nothing special to do. Just constrain the foreign keys and declare them as primary.

With the names of tables and columns you provided, here's a query to create the table you want :

CREATE TABLE review_table (
  movie_id INT,
  username VARCHAR(255),
  -- Change INT and VARCHAR(255) by what you used on the others tables
  PRIMARY KEY (movie_id, username),
  FOREIGN KEY (movie_id) REFERENCES movies_table(movie_id),
  FOREIGN KEY (username) REFERENCES users_table(username)
)

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.

How to create and handle composite primary key in JPA

You can make an Embedded class, which contains your two keys, and then have a reference to that class as EmbeddedId in your Entity.

You would need the @EmbeddedId and @Embeddable annotations.

@Entity
public class YourEntity {
    @EmbeddedId
    private MyKey myKey;

    @Column(name = "ColumnA")
    private String columnA;

    /** Your getters and setters **/
}

@Embeddable
public class MyKey implements Serializable {

    @Column(name = "Id", nullable = false)
    private int id;

    @Column(name = "Version", nullable = false)
    private int version;

    /** getters and setters **/
}

---

Another way to achieve this task is to use @IdClass annotation, and place both your id in that IdClass. Now you can use normal @Id annotation on both the attributes

@Entity
@IdClass(MyKey.class)
public class YourEntity {
   @Id
   private int id;
   @Id
   private int version;

}

public class MyKey implements Serializable {
   private int id;
   private int version;
}

Composite Primary Key and its Components

In the relational model, the individual attributes of a compound candidate key (any or every candidate key) are just attributes. But in the context of normalization, attributes are either prime or nonprime. Prime attributes are a part of one or more candidate keys. Nonprime attributes are not part of any candidate key.

Apart from normalization, we usually don't talk about prime and nonprime attributes.

---

Related Topics