Basic Table Creation

MySQL

  1. CREATE TABLE Documents
  2. (
  3.     doc_id INTEGER NOT NULL,
  4.     doc_name VARCHAR(100) NOT NULL,
  5.     doc_cost DECIMAL(10, 2)
  6. );

Working With NULL Values

CREATE TABLE Documents
(
    doc_id INTEGER NOT NULL,
    doc_name VARCHAR(100) NOT NULL,
    doc_cost DECIMAL(10, 2)
);

Specifying Default Values

CREATE TABLE Orders (
order_id INT(11) NOT NULL,
order_date DATE NOT NULL,
items_count INT(11) DEFAULT 1
)

Create With Primary Key

CREATE TABLE Documents
(
    doc_id INTEGER NOT NULL PRIMARY KEY,
    doc_name VARCHAR(100) NOT NULL,
    doc_cost DECIMAL(10, 2)
); 

-- or

CREATE TABLE Documents
(
    doc_id INTEGER NOT NULL,
    doc_name VARCHAR(100) NOT NULL,
    doc_cost DECIMAL(10, 2),
    PRIMARY KEY (doc_id)
);

-- or

CREATE TABLE Documents
(
    doc_id INTEGER NOT NULL,
    doc_name VARCHAR(100) NOT NULL,
    doc_cost DECIMAL(10, 2),
    CONSTRAINT PK_Documents PRIMARY KEY (doc_id)
);

Create With Foreign Key

CREATE TABLE Persons
(
    person_id INTEGER NOT NULL,
    person_name VARCHAR(100) NOT NULL,
    CONSTRAINT PK_Person PRIMARY KEY (person_id)
);

/*SQL Server / Oracle / MS Access*/
CREATE TABLE Orders
(
    order_id INTEGER NOT NULL,
    order_number INTEGER NOT NULL,
    person_id INTEGER NOT NULL FOREIGN KEY REFERENCES Persons(person_id),
    PRIMARY KEY (order_id)
);

--or

/*MySQL / SQL Server / Oracle / MS Access*/
CREATE TABLE Orders
(
    order_id INTEGER NOT NULL,
    order_number INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    PRIMARY KEY (order_id),
    CONSTRAINT FK_OrderPerson FOREIGN KEY (person_id)
    REFERENCES Persons(person_id)
);