--         File: contradiction_2a.sql
--      Version: 1.1
-- Last Changed: 2017-03-05
--           by: Damir;  https://www.damirsystems.com
--      Project: Contradiction in Database
--  Description: Part 2.1
--           DB: PostgreSQL


-- [p_1] Country named (CNAME) is assigned
-- country code (CC2) and number (CID).
--
create table Country
( CID   integer
, CC2   char(2)
, CNAME text

, CONSTRAINT pk_cn PRIMARY KEY (CID)
) ;


-- sample data with a typo
insert into Country (CID, CC2, CNAME)
values
   (1,  'CA', 'Canada')
 , (2,  'FR', 'France')
 , (3,  'DE', 'Germany')
 , (4,  'DE', 'Denmark')  -- typo
;


/*
-- later (see post) run this to
-- fix the data

update Country set
CC2 = 'DK'
where CNAME = 'Denmark' ;

alter table Country
  add constraint ak1_cn UNIQUE (CC2)
, add constraint ak2_cn UNIQUE (CNAME) ;

*/