Keys, Types & Constraints — Series 2

Preview — 3 of 10 questions

What is a natural key, in contrast to a surrogate key?

javascript

-- Natural key example
CREATE TABLE books (isbn TEXT PRIMARY KEY, title TEXT NOT NULL);

-- Surrogate key example
CREATE TABLE books (id SERIAL PRIMARY KEY, isbn TEXT UNIQUE, title TEXT NOT NULL);

AAn identifier that comes from real-world business data (e.g., an email address, a social security number, an ISBN) and has inherent meaning outside the database

BA key generated automatically by the database with no business meaning

CA key that references another table's primary key

DA key made up of exactly two columns

What is the key difference between a UNIQUE constraint and a PRIMARY KEY on the same column?

javascript

CREATE TABLE users (
  id       SERIAL PRIMARY KEY,        -- exactly one PRIMARY KEY per table; implies NOT NULL
  email    TEXT UNIQUE NOT NULL,      -- UNIQUE constraint, explicitly also NOT NULL here
  phone    TEXT UNIQUE                -- another UNIQUE constraint — multiple NULLs allowed
);

INSERT INTO users (email, phone) VALUES ('a@x.com', NULL); -- OK
INSERT INTO users (email, phone) VALUES ('b@x.com', NULL); -- also OK — NULLs don't conflict for UNIQUE

AThere is no difference — they are exactly interchangeable in every respect

BA PRIMARY KEY implies both uniqueness AND NOT NULL, and a table can have only one; a UNIQUE constraint also enforces uniqueness but allows NULL values (multiple NULLs), and a table can have several UNIQUE constraints

CUNIQUE constraints can only be applied to numeric columns

DPRIMARY KEY allows duplicate values as long as they're not adjacent

What does this self-referencing foreign key model?

javascript

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INT REFERENCES employees(id)
);

AEach employee references a row in a separate managers table

BThis is invalid syntax — a table cannot reference itself

CAn employee's manager_id points to another row in the very same employees table, modeling a hierarchical reporting structure without needing a separate table

DIt creates a many-to-many relationship between employees

Answer all 10 questions (7 more), see explanations for every answer, and track your score.