sql course 3 - unique constraint

sql-c3-unique-constraint.sql

CREATE TABLE "users1" (
	id SERIAL,
	name varchar(10)
	);

SELECT * FROM users1;

INSERT INTO "users1" ("name") VALUES ('user1');

-- insert another user

INSERT INTO "users1" ("name") VALUES('user2');

-- add a unique constraint to column "name"

ALTER TABLE users1 ADD UNIQUE("name");

-- or create our own name for the constraint

ALTER TABLE "users1" ADD CONSTRAINT "unique_user_name" unique("name");

/*
 sql_c3_ex=# \d users1
                                   Table "public.users1"
 Column |         Type          | Collation | Nullable |              Default               
--------+-----------------------+-----------+----------+------------------------------------
 id     | integer               |           | not null | nextval('users1_id_seq'::regclass)
 name   | character varying(10) |           |          | 
Indexes:
    "unique_user_name" UNIQUE CONSTRAINT, btree (name)


 */

-- add a duplicate user
INSERT INTO "users1" ("name") VALUES('user1');

/* 
 * The above command results in error
 * SQL Error [23505]: ERROR: duplicate key value violates unique constraint "unique_user_name"
  Detail: Key (name)=(user1) already exists.

Error position:
*/

-- try to update a existing user name to a possible duplicate
UPDATE "users1" SET name = 'user1' WHERE "id" = 2;

/*
	Results in the following error
	SQL Error [23505]: ERROR: duplicate key value violates unique constraint "unique_user_name"
  Detail: Key (name)=(user1) already exists.

Error position:
*/


-- constraint for multiple columns
CREATE TABLE leader_board (
	"game_id" INTEGER,
	"player_id" INTEGER,
	"rank" SMALLINT,
	UNIQUE("game_id", "rank")
);

-- the above table can have only one combination of game_id and rank

/*
 
sql_c3_ex=# \d leader_board;
              Table "public.leader_board"
  Column   |   Type   | Collation | Nullable | Default 
-----------+----------+-----------+----------+---------
 game_id   | integer  |           |          | 
 player_id | integer  |           |          | 
 rank      | smallint |           |          | 
Indexes:
    "leader_board_game_id_rank_key" UNIQUE CONSTRAINT, btree (game_id, rank)

*/

-- primary key constraints