CREATE TABLE statement

This post is aimed at Information Technology (“IT”) students in DBE and IEB schools. When learning the Structured Query Language (“SQL”) portion of the syllabus, ideally you want to be using Microsoft SQL Server, or MySQL. If you are using Microsoft Access, you should specifically be aware of the difference in data types and SQL syntax.

1. Basic syntax

Creating a table in MySQL is as easy as can be! The syntax is as follows:

CREATE TABLE tblMyTableName
(
	fieldName1 datatype,
	fieldName2 datatype,
	fieldName3 datatype,
	fieldName4 datatype
);

CREATE TABLE tblMyTableName
(
	fieldName1 datatype,
	fieldName2 datatype,
	fieldName3 datatype,
	fieldName4 datatype
);

Below is an example of a complete CREATE TABLE statement:

CREATE TABLE tblCustomers
(
	customerID INT(255),
	customerSurname VARCHAR(100),
	customerCellnumber CHAR(10),
	customerCreditApproved BOOLEAN
);

CREATE TABLE tblCustomers
(
	customerID INT,
	customerSurname TEXT(100),
	customerCellnumber TEXT(10),
	customerCreditApproved YesNo
);

2. Additional syntax

To add a Primary Key (“PK”), the PK field must be declared NOT NULL and the PRIMARY KEY constraint must be added at the end of the statement:

CREATE TABLE tblCustomers
(
	customerID INT NOT NULL,
	customerSurname VARCHAR(100) NOT NULL,
	customerCellnumber CHAR(10),
	customerCreditApproved BOOLEAN,
	PRIMARY KEY (customerID)
);

CREATE TABLE tblCustomers
(
	customerID INT NOT NULL,
	customerSurname TEXT(100) NOT NULL,
	customerCellnumber TEXT(10),
	customerCreditApproved YesNo,
	PRIMARY KEY (customerID)
);

3. Data types

The most common data types:

• CHAR(size)
• VARCHAR(size)
• INT(size)
• BIGINT(size)
• BOOLEAN
• DATE
• TIMESTAMP

There are small (and some not-so-small) differences between data types in MySQL, SQL Server and Microsoft Access. Below is a selection of data types and their approximate equivalents:

For a full list of data types, consult https://www.w3schools.com/sql/sql_datatypes.asp

References:

1. o365devx (no date) Create and delete tables and indexes using Access SQL, Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/access/concepts/structured-query-language/create-and-delete-tables-and-indexes-using-access-sql (Accessed: 21 April 2024).
2. W3schools. (2023). SQL CREATE TABLE Statement. Available at: https://www.w3schools.com/sql/sql_create_table.asp (Accessed: 13 February 2023).
3. W3schools. (2023). SQL PRIMARY KEY Constraint. Available at: https://www.w3schools.com/sql/sql_primarykey.asp (Accessed: 16 February 2023).
4. W3schools. (2023). SQL Data Types for MySQL, SQL Server, and MS Access. Available at: https://www.w3schools.com/sql/sql_datatypes.asp (Accessed: 14 February 2023).
5. Raskew (2008) ‘Create Table’ and Yes/No Field, Access World Forums. Access World Forums. Available at: https://www.access-programmers.co.uk/forums/threads/create-table-and-yes-no-field.152693/#post-723678 (Accessed: 21 April 2024).