SQL: Data Types

This entry is part 12 of 20 in the series SQL Tutorial

When creating a table, we always have to decide on the right data type for use in the columns.

CREATE TABLE IF NOT EXISTS products (
         productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
         productCode  CHAR(3)       NOT NULL DEFAULT '',
         name         VARCHAR(30)   NOT NULL DEFAULT '',
         quantity     INT UNSIGNED  NOT NULL DEFAULT 0,
         price        DECIMAL(7,2)  NOT NULL DEFAULT 99999.99,
         PRIMARY KEY  (productID)
       );

We define 5 columns in the table products.

The data types are:

  • productID is int unsigned – non-negative integers.
  • productCode is char(3) – a fixed-length alphanumeric string of 3 characters.
  • name is varchar(30) – a variable-length string of up to 30 characters.
  • quantity is also int.
  • price is decimal(7, 2) – a decimal number with 2 decimal places with a maximum of 7 digits.

The attribute not null specifies that the column cannot contain the null value.

We set the column productID as the so-called primary key. Values of the primary-key column must be unique.

Every table shall contain a primary key. This ensures that every row can be distinguished from other rows. An index is build automatically on the primary-key column to facilitate fast search.

Series Navigation<< SQL: Insert current time or timestamp
SQL: char data type >>