How to Define a Composite Primary Key in SQL?

SQL composite primary key definition

In SQL, primary keys are used to uniquely identify rows in a table. A composite primary key consists of multiple fields. Here's how to define a composite primary key that contains two fields:

Question:

Create a table named voting with fields including QuestionID, MemberID and vote. Define a composite primary key using QuestionID and MemberID.

Solution:

To create a composite primary key in SQL, use the following syntax:

<code class="language-sql">CREATE TABLE table_name (
  field_name_1 data_type,
  field_name_2 data_type,
  PRIMARY KEY (field_name_1, field_name_2)
);</code>

In this example, the SQL statement to create a voting table with a composite primary key is as follows:

<code class="language-sql">CREATE TABLE voting (
  QuestionID NUMERIC,
  MemberID NUMERIC,
  vote VARCHAR(255),
  PRIMARY KEY (QuestionID, MemberID)
);</code>

Instructions:

The
• PRIMARY KEY clause specifies that the combination of QuestionID and MemberID will become the primary key of the voting table.
The
• (QuestionID, MemberID) pair must be unique among all rows of the table.
• Neither QuestionID nor MemberID can be NULL.
• If the query uses both QuestionID and MemberID in the WHERE clause, it will use the primary key index to speed up the lookup.
• It is recommended to choose the order of fields in the primary key based on the expected usage pattern of the table.

The above is the detailed content of How to Define a Composite Primary Key in SQL?. For more information, please follow other related articles on the PHP Chinese website!