Primary key without duplicated values

Question

i'm trying and success to create primary key in Redshif

   create table my_table(id int ,
    primary key(id));


insert into my_table values
(id),
(1),
(1),
(20);

select count(*) from my_table

3

but it allows me to upload duplicated value ,

as far as i know primary key should contain unique values ,

did i do something wrong?

Can you show a full snippet of that works (although it's supposed to fail)? A primary key should indeed prohibit duplicate values. — Mureinik, Feb 23 '17 at 10:47

score 1 · Accepted Answer · edited May 23 '17 at 12:26

1

you can find your answer here

How to create an Index in Amazon Redshift

one of the answers mention your problem with the primary key

edited May 23 '17 at 12:26

Community

1
1

answered Feb 23 '17 at 11:31

user3600910

2,839
4
22
36

score 0 · Answer 2 · answered May 17 '17 at 18:39

Create an identity key which will act as an auto_increment surrogate key. This'll serve both the purpose - to uniquely identify the records in the table and prevent insertion of duplicate values.

Let's create a dummy table:

create table scratchpad.test_1
(id bigint identity(1,1),
name varchar(10));

insert into scratchpad.test_1(name)
values
('a'),('b'),('c'),('d');

select * from scratchpad.test_1;

The id column acts as a primary key. Deleting any record from the table does not impact the sequencing of other values and the id column can be used to uniquely identify the subsequent row.

Primary key without duplicated values

2 Answers2