MySQL cheatsheet: create a table

create_table.sql

CREATE TABLE Teachers (name VARCHAR(30), age INT);

-- Primary key:
CREATE TABLE Teachers (id INT PRIMARY KEY, name VARCHAR(30), age INT); # primary key: main index, can't be repeated
CREATE TABLE Students (id INT, name VARCHAR(30), age INT, PRIMARY KEY (code, name, age)); # primary key is compound
CREATE TABLE Subjects (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(30)); # primary key is automatically generated, starting in 1 and following 2,3...

-- Restrictions:
CREATE TABLE Teachers (id INT PRIMARY KEY NOT NULL, name VARCHAR(30) NOT NULL, age INT NOT NULL); # NOT NULL: the column value can't be empty
CREATE TABLE Teachers (id INT PRIMARY KEY, name VARCHAR(30), age INT, CHECK (age>18 and age<70)); # CHECK: the column value must comply some conditions
CREATE TABLE Students (id INT, name VARCHAR(30), age INT, passport VARCHAR (20), UNIQUE(passport)); # UNIQUE: there can't be more than one row with the same value in that column

-- Foreign keys:
CREATE TABLE Studies (student INT NOT NULL, subject INT NOT NULL, FOREIGN KEY (student) REFERENCES Students(id), FOREIGN KEY (subject) REFERENCES Subjects(id)); # foreign keys: relationships between relations (tables)

-- Default values:
CREATE TABLE Students (id INT, name VARCHAR(30), age INT, country VARCHAR(20) DEFAULT "Spain"); # if the "country" attribute is not specified, it's stored with default value "Spain"