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:

Data TypeSQL
CharacterCHAR, VARCHAR, CLOB
BinaryBINARY, VARBINARY, BLOB
Numeric (exact)NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT
Numeric (approximate)FLOAT, REAL, DOUBLE
TimeDATE, TIME, TIMESTAMP
OtherINTERVAL, 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:

ConstraintDescription
CHECKEnsure all column values satisfy specific conditions.
DEFAULTSet a column value when none is specified.
FOREIGN KEYUniquely identifies a row/record in another table.
INDEXOptimize for speedy data retrieval.
NOT NULLEnsure the column value contains a non-NULL value.
PRIMARY KEYUniquely identify each row with a combination of NOT NULL and UNIQUE.
UNIQUEEnsure 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 (
symbolVARCHAR(6)NOT NULL UNIQUE,
nameVARCHAR(40) NOT NULL,
INDEX(symbol) ) ;
 
INSERT INTO companies
VALUES
( "GBTC", "Grayscale" ),
( "MSTR", "MicroStrategy" ),
( "TSLA", "Tesla" ) ;

SELECT * FROM companies ;
symbolname
GBTCGrayscale
MSTRMicroStrategy
TSLATesla

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 (
    transactionINTNOT NULL AUTO_INCREMENT,
    buyerVARCHAR(50),
    ageINTCHECK (age >= 18),
    companyVARCHAR(33),
    amountFLOATDEFAULT 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 ;
transactionbuyeragecompanyamount
1Barry Silbert43GBTC449596
2Michael Saylor56MSTR71079
3Elon Musk49TSLA44776119

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.

Conclusion

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 AUTO_INCREMENT, whereas FOREIGN KEY looks outward to other tables for integrity support.

Start Learning

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.