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.