-1

I know we can apply primary key to a column to provide uniqueness for a row and we can apply multiple primary keys and get a composite key.

But this didn't work for my case. I have userID and email columns. And I want them to be unique at the same time. When I applied primary key attribute to both of them a person with same email but different userID can be added to the table. So composite key is like a combination I suppose, when userID changes there is no need to change email.

I am using psycopg2 module with python and I can do this uniqueness while user registration by searching rows if there is a same email and ask the user for change in email for uniqueness. But I want to learn is there way to do this kind of separate uniqueness with postgresql/sql.

Thanks in advance...

Necip Asım ARSLAN
  • 123
  • 1
  • 8

2 Answers2

2

No need to make it an id! Just make it a unique constraint. For example like this:

CREATE TABLE orders(
id integer PRIMARY KEY,
email VARCHAR(255) UNIQUE
)
Waidmann
  • 74
  • 2
0

You can achieve this by using the Unique constraint. You can use it as below:

CREATE TABLE person (
   id serial PRIMARY KEY,
   first_name VARCHAR (50),
   last_name VARCHAR (50),
   email VARCHAR (50) UNIQUE
);

In case of primary key you cant keep null values but with unique you can have null as long as it is not duplicated across rows. Primary key is a combination of NOT NULL and Unique constraint.

Seeker90
  • 785
  • 4
  • 17
  • 37