The two constraints every table needs
Difficulty 1/5Free
New to this? Learn it first: Keys: what makes a row that rowNew to this? Learn it first: Ids the database makes, and keys of two columnsNew to this? Learn it first: NULL: the value that is not a value
A company keeps a directory of its employees.
HR has two rules, and both are the kind nobody writes down until somebody breaks them:
- Every employee has a name and a work email. A record missing either one is useless: payroll cannot pay it and nobody can contact it.
- No two employees share a work email, and no two share a badge number. The badge opens doors and the email receives payslips, so a duplicate is not untidy, it is a security problem.
The table already holds all four values. It just does not enforce anything yet.
The contract
1 relationThe tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.
employees
idfull_namework_emailbadge_number
Nothing here yet. Run the tests while signed in and every distinct schema you submit is kept, newest first, so an earlier design can be read next to the one you would write now.
PostgreSQL DDL: tables, constraints, indexes
schema.sql
Tab indents · ⌘/Ctrl + Enter runs
What will be checked
6 tests- ····A complete employee record goes in
- ····An employee without a name is refused
- ····An employee without a work email is refused
- ····Two employees cannot share a work email
- ····Two employees cannot share a badge number
- ····A different employee still goes in