How to make separately unique each column in postgresql?

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…

Answer

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
)

Advertisement

Answer