One account per email address

Let a UNIQUE constraint keep duplicate accounts out.

What to build

A users table that accepts each email address only once.

Requirements

• Create users with id INTEGER PRIMARY KEY, an email that must be given and must be unique, and a name that must be given.
• Copy the sign-ups in with the INSERT OR IGNORE given in the starter.
• Show every column of users, in order of id.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE signups (
  id INTEGER PRIMARY KEY,
  email TEXT,
  name TEXT NOT NULL
);

Output

Columns id, email and name, in order of id. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Emails are compared exactly, so A@mail and a@mail count as different.

Example

When a test starts with:

INSERT INTO signups VALUES (1, 'a@mail', 'Asha'), (2, 'b@mail', 'Bilal'), (3, 'a@mail', 'Again');

your SQL should print:

id  email   name
--  ------  -----
1   a@mail  Asha
2   b@mail  Bilal
(2 rows)

Why it works

The first row with an email gets in. Any later row with the same email breaks UNIQUE and is skipped, and because a skipped row takes no id, the ids stay 1, 2, 3 and so on.

Examples

CREATE TABLE signups (
  id INTEGER PRIMARY KEY,
  email TEXT,
  name TEXT NOT NULL
);
INSERT INTO signups VALUES (1, 'a@mail', 'Asha'), (2, 'b@mail', 'Bilal'), (3, 'a@mail', 'Again');

id  email   name
--  ------  -----
1   a@mail  Asha
2   b@mail  Bilal
(2 rows)

CREATE TABLE signups (
  id INTEGER PRIMARY KEY,
  email TEXT,
  name TEXT NOT NULL
);
INSERT INTO signups VALUES (1, NULL, 'Nobody'), (2, 'c@mail', 'Chen');

id  email   name
--  ------  ----
1   c@mail  Chen
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

INSERT OR IGNORE INTO users (email, name)
SELECT email, name FROM signups ORDER BY id;

SELECT * FROM users ORDER BY id;

Lesson: Keys and constraints →