5

My Problem is to make the Column email NOT NULL AND UNIQUE

ALTER TABLE benutzer ADD email VARCHAR(75) UNIQUE;

works fine but

ALTER TABLE benutzer ADD email VARCHAR(75) UNIQUE NOT NULL;

or

ALTER TABLE benutzer ADD email VARCHAR(75) UNIQUE ,NOT NULL;

work not fine :-(

the error is:

FEHLER: Syntaxerror by „NULL“

I don't understand the Problem from Postgresql >.<

EDIT:

I have solve the Problem. The Solution for my Problem is to delete the records in my Table.

user3763754
  • 89
  • 1
  • 1
  • 3
  • 1
    @BrianDeMilia Re your deleted answer, it's actually not correct to say that `UNIQUE NOT NULL` is equivalent to `PRIMARY KEY`. There can be only one `PRIMARY KEY` and it is identified in `INFORMATION SCHEMA` etc. So while `PRIMARY KEY` is indeed `UNIQUE NOT NULL`, the reverse isn't true - a `UNIQUE NOT NULL` column isn't necessarily a `PRIMARY KEY`. – Craig Ringer Jul 22 '14 at 02:02
  • 2
    "ALTER TABLE benutzer ADD email VARCHAR(75) UNIQUE NOT NULL;" works for me. What error do you see? – harmic Jul 22 '14 at 02:02
  • 1
    Are you actually using PostgreSQL proper? Please show `SELECT version`, and also the **full, exact text** of the `UNIQUE NOT NULL` error by editing your question and copy and pasting the error. – Craig Ringer Jul 22 '14 at 03:55
  • 3
    The comma in `, NOT NULL` in the third example is definitely wrong. But you can't add a `NOT NULL` column without a default value if there are existing rows in the table. –  Jul 22 '14 at 06:25

1 Answers1

1

It is not possible to add a new column that is both UNIQUE and NOT NULL at the same time, when it contains existing records.

The reason for this is due to the fact that adding a column gives it null values to start with, and simultaneously saying there are not allowed to nulls is a contradiction.

  1. The solution is to remove your data, add the column then add the data back

OR

  1. create unique default data for the new column

OR

  1. create the column first, generate unique data for it, then add NOT NULL afterwards

for reference another answer with more info: Create unique constraint with null columns

Zaffer
  • 1,290
  • 13
  • 32