CODEWITHSUNDEEP
sqlamazon-redshift
Eran Levi
Eran Levi

Reputation: 33

Primary key without duplicated values

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?

Upvotes: 3

Views: 291

Answers (2)

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

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;

enter image description here

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.

enter image description here

Upvotes: 0

user3600910
user3600910

Reputation: 3109

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

Upvotes: 1

Related Questions