Tables contain information that you can insert, select, update. Before even doing that, you need to create these tables.
To create a table, you need to know about columns’ types
In the lesson about tables, columns and rows, we saw that:
- tables have lines (rows).
- tables have columns.
- the intersection between a table and a column is a field.
All fields within a column have the same type. A field type tells you:
- if the field contain text or numbers.
- the biggest amount of information it can hold.
In order to create a table, you have to show for each column:
- the name of the column.
- the type of fields in this column.
Optionally, you can indicate other properties of the column, like:
- an indication that the field cannot contain null values. The syntax for this is : NOT NULL.
- the default value taken by the column when inserting a new row without specifying the value to put in this column. The syntax for this is, in case of a text field: DEFAULT ‘defaultstringvalue’. In case of a number, the syntax is for example DEFAULT 5.
- the indication that the column is the table’s primary key. Such a column contains in each row a text or a number that can identify a row uniquely. Two rows cannot have the same value in this column, and this value cannot be null. There can be only one primary key per table. To define a table as a primary key, add the text PRIMARY KEY just after the definition of the column’s type.
- the indication that the value column is automatically generated when inserting a new row. Its value is then equal to the highest value in this column plus an increment (usually the increment is equal to 1). The syntax for this is AUTO_INCREMENT.
Syntax to create a table
Example of table creation with mySQL:
Example of table creation with SQL Server:
In this example:
- there are 6 columns (ID, total_sales, first_name, last_name, state, status).
ID intmeans that fields in column ID contain integers. An integer is a number without any decimal point. Example: -1, 1, 2, 3,…. are integers. 1.5 (one and a half) is not an integer.
NOT NULL AUTO_INCREMENT PRIMARY_KEYmeans that fields in the column cannot be null and are generated automatically while inserting data by adding an increment to the highest value in the column. PRIMARY_KEY means that the column ID is the table’s primary key. In SQL server, AUTO_INCREMENT is replaced by IDENTITY(1,1).
first_name varchar(30)means that the fields in column first_name can contain from 0 to 30 characters. A character can be a letter, a number, or a special character (for example “&”).
total_sales decimal(7,2)means that the fields in column total_sales contain numbers that can have digits after the decimal point. Example: in 10.5 (ten and a half), 5 is a digit after the decimal point. These numbers cannot have more than 7 digits. From these 7 digits, 2 at the most can be after the decimal point (“.”). For example, the number “19878.51 “ has 7 digits in total(1,9,8,7,8,5 and 1), 2 digits being after the decimal point (5 and 1). The numbers 123456.12 and 1234.123 cannot be contained in this field: 123456.12 has too many digits in total (8) and 12345.123 has too many digits after the decimal point (3).
status varchar(20) DEFAULT 'approved'means that the fields in column status is set by default to ‘approved’ when inserting a new row, if the value for this field is not defined in the INSERT statement.
Why I should bother thinking about the size of a field ? Can I not just reserve a huge size for each of them ? :
The bigger the field size, the more information it can hold, but the more place it takes. A bit like a box. To save space, use the smallest field size you can.
A text field can contain numbers. Why can I not just always specify that the column should contain text?
Some operations like calculating totals and averages, comparing numbers are possible only when the field contains numbers.