The rules for the names and columns can be a bit different depending on the type of database so it is important to consider looking at the documentation from the database that you intend to use. There are some common rules that are consistent across many different databases. These include:
For each column or field in a table, we must identify the type of data that the column can store along with its length in some cases. The most common data types are:
Now that we have some of the core aspects to get started, we can move to the CREATE TABLE syntax. The SQL CREATE TABLE command makes a new table through the process of defining the layout of the table. Similar to the SELECT statement that you’ve learned about, there is a standard structure for the format of the CREATE TABLE statement. After we have the CREATE TABLE statement, we then have the name of the table, the names, data types, and lengths of the columns.
The basic syntax looks like the following:
CREATE TABLE <tablename> ( <column1> <datatype> [constraint], <column2> <datatype> [constraint], … );
In the sample above, the <tablename> and <column> would need to be replaced with the actual table name and column. The <datatype> would be replaced with the data type and size if necessary. The [constraint] is optional and we will get into the different options in the next tutorial. For now, the only constraint you need to be aware of is the PRIMARY KEY constraint. The PRIMARY KEY constraint uniquely identifies a row in a table. There is an open parenthesis after the <tablename> to signify the starting of the column list and then a close parenthesis after all of the columns and constraints have been created. If the CREATE TABLE statement is missing either one, it will raise an error.
To make the code easier to read, it is good practice to have one line per column or attribute. If we wanted to create a new table called contact that had the contact_id as the primary key and two additional columns with the username and password, the statement could look like the following:
CREATE TABLE contact( contact_id int PRIMARY KEY, username VARCHAR(50), password VARCHAR(50) );
In doing so and running the command, you should see a message similar to the following:
In the Schema Browser, you should see the contact table listed after the album and artist table:
A table could have just one column or many columns, it depends on what you are trying to create. For example, you could have a newsletter table that just consists of emails:
CREATE TABLE newsletter( email VARCHAR(50) );
Although this is the basic syntax, there are other criteria that we’ll explore in upcoming tutorials.