INSERT Statement Syntax and Options
Role normalization separates role information into a dedicated Role table, eliminating redundancy and centralizing role management.
Why Separate Roles
Suppose many Member rows need to record roles such as administrator, editor, or reviewer. Role normalization moves role information into a dedicated Role table instead of repeating that information throughout the Member table. The Member table then stores a role_id value that points to the appropriate Role record.
Role Table Constraints
The normalized design gives the Role table two important constraints. Its id column is the PRIMARY KEY, identifying each Role record. Its name column is UNIQUE, so role names cannot be repeated. The Member table does not store the role name as its link; it uses role_id to reference the Role table's id column.
| Table | Column | Constraint or role |
|---|---|---|
| Role | id | PRIMARY KEY |
| Role | name | UNIQUE |
| Member | role_id | Foreign key referencing Role.id |
The columns and constraints used to centralize role information
PRIMARY KEY and UNIQUE both restrict duplication, but they apply to different columns in this design: the Role id column is the primary identifier, while the Role name column must also remain unique.
Member-to-Role Linking
A Member row points to a Role row through role_id. The value in Member.role_id must identify an existing Role record. This is the purpose of the foreign key constraint: it enforces referential integrity and prevents a Member row from referring to a Role that does not exist.
Connecting a member to an existing role
A Member must be assigned the Role record whose id represents the editor role.
Find the Role record: Use the existing Role record identified by its id. The Role table centralizes the role information.
Store the identifier: Insert the corresponding identifier into the Member row's role_id column.
Check the reference: The foreign key requires the referenced Role record to exist. A Member row cannot point to a non-existent Role record.
The Member row is linked to the centralized Role record through role_id.
Choosing INSERT Columns
When inserting a row, the important choice for a PRIMARY KEY column is whether to supply a value or omit the column. If you omit the PRIMARY KEY value, the database can generate it. If you include the column, you are manually assigning its value, and that value must satisfy the table's constraints.
Inserting a Role without choosing its id
Add a new Role named editor while allowing the database to generate the Role id.
Omit the PRIMARY KEY column: The insertion supplies the role information but does not manually assign Role.id.
Allow generation: Because the PRIMARY KEY value is omitted, the database can generate it.
Use the generated identifier: The resulting Role identifier is the value that a related Member row can use in role_id.
A Role record is added without manually choosing its PRIMARY KEY value.
Manual Primary Keys
A PRIMARY KEY value may also be assigned manually during insertion. The assigned value must be unique and must not already be present in the table. If it duplicates an existing PRIMARY KEY value, the insertion violates the PRIMARY KEY constraint. Manual assignment is therefore an option with a restriction, not permission to reuse any identifier.
What do you think happens?
A Role table already contains a row with a particular id. What should happen if another insertion manually assigns that same id?
Reveal answer
Answer: The insertion violates the PRIMARY KEY constraint.
The source requires manually assigned PRIMARY KEY values to be unique and not already present.
Mistakes with Insertions
Repeating role names in Member rows instead of centralizing them in Role.
The normalized design is intended to separate role information into a dedicated Role table and eliminate redundancy.
Fix:
Store the role once in Role and connect each Member through role_id.Using a role_id that does not identify an existing Role record.
The foreign key enforces referential integrity and prevents references to non-existent Role records.
Fix:
Reference an existing Role record.Manually assigning a PRIMARY KEY value that already exists.
PRIMARY KEY values must be unique and not already present.
Fix:
Use a new unique value or omit the PRIMARY KEY so the database can generate it.Assuming that every insertion must manually provide a PRIMARY KEY.
PRIMARY KEY values can be omitted to allow database generation.
Fix:
Omit the PRIMARY KEY column when you want the database to generate its value.
Practice Decisions
For each situation, decide whether the insertion should manually assign the PRIMARY KEY or omit it: adding a new Role when the database should generate identifiers; adding a Role with a deliberately chosen unused identifier; adding a Role using an identifier already present; and adding a Member whose role_id points to no existing Role.
Hints
- Omitting the PRIMARY KEY allows database generation.
- A manually assigned PRIMARY KEY must be unique and not already present.
- A Member foreign key must reference an existing Role record.
Checking four insertion choices
Evaluate the four practice situations using the Role and Member constraints.
Generated Role identifier: Omit the Role PRIMARY KEY value so the database can generate it.
Unused manual identifier: Manual assignment is allowed when the chosen PRIMARY KEY value is unique and not already present.
Existing manual identifier: Reject the choice because a duplicate PRIMARY KEY value violates the constraint.
Missing referenced Role: Reject the Member reference because the foreign key cannot point to a non-existent Role record.
The valid choices depend on both insertion behavior and the constraints protecting Role and Member.
Key Takeaways
- Role normalization places role information in a dedicated Role table, reducing redundancy and centralizing role management.
- Role.id is the PRIMARY KEY, Role.name is UNIQUE, and Member.role_id references Role.id through a foreign key.
- A PRIMARY KEY value can be omitted during insertion so the database can generate it.
- A manually assigned PRIMARY KEY must be unique and must not already exist.
- Foreign key enforcement prevents Member rows from referring to non-existent Role records.
Key Takeaways
- Normalize repeated role information by creating a separate Role table.
- Use Role.id as the PRIMARY KEY and Role.name as a UNIQUE column.
- Link Member to Role with the Member.role_id foreign key.
- Omit a PRIMARY KEY value when the database should generate it.
- Manually supplied PRIMARY KEY values must be unique and not already present.