Relational databases store information in tables — with columns that are analogous to elements in a data structure and rows which are one instance of that data structure — which are brought into existence via the SQL Create Table statement. When creating tables, each column specified may have two characteristics:
- type — what kind of data can be held
- constraint — restrictions on the data
This blog entry covers many of the common SQL data types and the constrains that may be placed upon them. Practical examples and source code is provided. Table structure may be changed after creation with the SQL Alter statement.
SQL Data Types
Because database vendors choose which ANSI standard SQL to implement, and the standard evolves, you’ll have to check your specific database for the data types and synonyms accepted. In general, all databases support the following data types:
|Character||CHAR, VARCHAR, CLOB|
|Binary||BINARY, VARBINARY, BLOB|
|Numeric (exact)||NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT|
|Numeric (approximate)||FLOAT, REAL, DOUBLE|
|Time||DATE, TIME, TIMESTAMP|
|Other||INTERVAL, BOOLEAN, XML, JSON|
SQL Data Constraints
Constraints are optional rules restricting data entered into the column being described. As database vendors can customize their offerings, check which constraints are supported. Common constraints are:
|CHECK||Ensure all column values satisfy specific conditions.|
|DEFAULT||Set a column value when none is specified.|
|FOREIGN KEY||Uniquely identifies a row/record in another table.|
|INDEX||Optimize for speedy data retrieval.|
|NOT NULL||Ensure the column value contains a non-NULL value.|
|PRIMARY KEY||Uniquely identify each row with a combination of NOT NULL and UNIQUE.|
|UNIQUE||Ensure all values in the column are different.|
Basic SQL Create Table & Constraint Examples
The SQL Create Table statement looks like:
CREATE TABLE table_name ( column_1 data_type column_constraint, column_2 data_type column_constraint, column_3 data_type column_constraint, ... ) ;
To illustrate, let’s define a table for companies and their stock ticker symbols. Both the ticker symbol and the company names are constrained to contain something (i.e.
NOT NULL) and the symbols must be unique (just as they are in real-world stock markets).
CREATE TABLE companies ( symbol VARCHAR(6) NOT NULL UNIQUE, name VARCHAR(40) NOT NULL, INDEX(symbol) ) ; INSERT INTO companies VALUES ( "GBTC", "Grayscale" ), ( "MSTR", "MicroStrategy" ), ( "TSLA", "Tesla" ) ; SELECT * FROM companies ;
Another table we’ll fill with corporate officers buying assets, their companies, biographic age information, and the purchase amounts. We constrain the ages to ensure the officers are legal adults, we add a default value to the purchase amount, and cause the database to create a transaction ID — a monotonically increasing integer value — which we identify as a primary key (for speedy lookup). We link the purchases and companies tables via the company ticker symbol as a foreign key (to validate the symbols).
CREATE TABLE purchases ( transaction INT NOT NULL AUTO_INCREMENT, buyer VARCHAR(50), age INT CHECK (age >= 18), company VARCHAR(33), amount FLOAT DEFAULT 0.0, PRIMARY KEY (transaction), FOREIGN KEY (company) REFERENCES companies(symbol) ) ; INSERT INTO purchases (buyer, age, company, amount) VALUES ( "Barry Silbert", 43, "GBTC", 449596 ), ( "Michael Saylor", 56, "MSTR", 71079 ), ( "Elon Musk", 49, "TSLA", 44776119 ) ; SELECT * FROM purchases ;
Constraints Protecting Data Integrity
Attempting to insert a row into the purchases table with a company string which doesn’t appear in the companies table will cause a data validation error (as should be expected given the
FOREIGN KEY constraint). For example:
INSERT INTO purchases (buyer, age, company, amount) VALUES ( "Steve Jobs", 99, "AAPL", 0 ) ;
returns the following error, showing the foreign key constraint has protected the data integrity of the table values.
Cannot add or update a child row: a foreign key constraint fails (`database`.`purchases`, CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`company`) REFERENCES `companies` (`symbol`))
Attempting to insert an age value less than 18 will cause a similar error.
SQL Create Table From
As these tables are now “in production” — and therefore not to be tampered with — to create a test table and populate it with some of the date currently in the purchases table we can:
CREATE TABLE purchases_copy AS SELECT company, amount FROM purchases ;
SQL Drop Table
The counterpart to creating tables is the SQL Drop Table statement, which deletes the table structure and the data within.
SQL Create Table creates new tables and can copy existing ones. Various data types may be specified for each column and optional constraints provide integrity and data validation checking. Some constraints are inward-looking, like
FOREIGN KEY looks outward to other tables for integrity support.