25-primary-keys.sql

-- Generate a primary key using SERIAL or IDENTITY
CREATE TABLE users (
	user_id SERIAL PRIMARY KEY,
	username VARCHAR(100)
);

CREATE TABLE users_2 (
	user_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	username VARCHAR(100)
);

CREATE TABLE users_3 (
	user_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	username VARCHAR(100)
);

CREATE TABLE users_4 (
	user_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 2) PRIMARY KEY,
	username VARCHAR(100)
);

-- Composite primary keys
CREATE TABLE users_dual (
	id_1 int,
	id_2 int,
	PRIMARY KEY (id_1, id_2)
);

SELECT *
FROM users_dual;

-- Generate UUID as primary key
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
DROP EXTENSION "uuid-ossp";

SELECT gen_random_uuid(), uuid_generate_v4();

-- Generate a sequence
DROP SEQUENCE user_sequence;
CREATE SEQUENCE user_sequence;

SELECT 
	CURRVAL('user_sequence'), 
	NEXTVAL('user_sequence'), 
	CURRVAL('user_sequence');

CREATE TABLE users_6 (
	user_id INTEGER PRIMARY KEY DEFAULT NEXTVAL('user_sequence'),
	username VARCHAR(100)
);

SELECT *
FROM users_6;