SQL Intermediate Database

Foreign key relationship

How do I relate posts to users?

FOREIGN KEY constraint.

CREATE TABLE posts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  CONSTRAINT fk_posts_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
);

Notes

FKs protect referential integrity.

Keywords: foreign key · relation · references

SQL

CREATE DATABASE

How do I create a MySQL database?

SQL

ALTER TABLE add column

How do I add a column to an existing table?

SQL

COUNT with GROUP BY

How do I count rows per category?

SQL

LIKE search pattern

How do I search text with a keyword?

SQL

Basic transaction

How do I commit or roll back multiple writes?

SQL

WHERE with AND / OR

How do I combine filters in SQL?