Creating a table with a compound primary key

The syntax for creating tables with compound primary keys is a bit different from the single-column primary key syntax we saw in the previous chapter. We create a user_status_updates table with a compound primary key, as follows:

CREATE TABLE "user_status_updates" ( 
  "username" text, 
  "id" timeuuid, 
  "body" text, 
  PRIMARY KEY ("username", "id") 
);

In particular, instead of appending a single-column definition with a PRIMARY KEY modifier, we make a separate PRIMARY KEY declaration at the end of the list of columns, which itself specifies the two columns that make up the compound primary key.

While this is the only way to declare a compound primary key, it's also a perfectly valid way to declare a single-column primary key. So, our users table from the previous chapter could have been declared like this:

CREATE TABLE "users" ( 
  "username" text, 
  "email" text, 
  "encrypted_password" blob, 
  PRIMARY KEY ("username") 
);

Here, we simply move the PRIMARY KEY declaration to the end of the column list. It's a little less concise, but it has the exact same effect as a single-column definition with a PRIMARY KEY modifier.