 COMMIT and ROLLBACK | Structured Query Language Create Table | Data Types |
One of the basic steps during database development cycles is the fixing of decisions about the table structure. To do so, there is the CREATE TABLE statement with which developers define tables together with their columns and constraints.
Because a lot of features may be activated by the command, its syntax is a little bit complex. This page shows the most important parts. The syntax is not straight forward. At some points it is possible to use alternative formulations to express the same purpose, e.g. the Primary Key may be defined within the column definition as a column constraint, at the end of the command as a table constraint or as a separate stand-alone command 'ALTER TABLE ADD CONSTRAINT ...;' .
CREATETABLE<tablename>(<column_name><data_type><default_value><identity_specification><column_constraint>,<column_name><data_type><default_value><column_constraint>,...,<table_constraint>,<table_constraint>,...);
After the introductory key words CREATE TABLE, the tablename is specified. Within a pair of parentheses, a list of column definitions follows. Each column is defined by its name, data type, an optional default value, and optional constraints for this individual column.
After the list of column definitions, developers can specify table constraints like Primary and Foreign Keys, Unique conditions, and general column conditions.
An first example was shown at the pageCreate a simple Table and a second one here:
CREATETABLEtest_table(-- define columns (name / type / default value / column constraintidDECIMALPRIMARYKEY,part_numberCHAR(10)DEFAULT'n/a'NOTNULL,part_nameVARCHAR(500),stateDECIMALDEFAULT-1,-- define table constraints (eg: 'n/a' shall correlate with NULL)CONSTRAINTtest_checkCHECK((part_number='n/a'ANDpart_nameISNULL)OR(part_number!='n/a'ANDpart_nameISNOTNULL)));
The table consists of 4 columns. All of them have a data type and some a default value. The columnid acts as the Primary Key. The table constrainttest_check guarantees thatpart_name is mandatory ifpart_number is recorded.
The standard defines a lot of predefined data types: character strings of fixed and variable size, character large objects (CLOB), binary strings of fixed and variable size, binary large objects (BLOB), numeric, boolean, datetime, interval, xml. Beyond, there are complex types like: ROW, REF(erence), ARRAY, MULTISET and user-definded types (UDT). The predefined data types are explained on thenext page. To keep things simple, we use on this page only CHAR, VARCHAR, and DECIMAL.
A column can have a default value. Its data type corresponds to the type of the column. It may be a constant value like the number -1 or the string 'n/a', or it is a system variable or a function call to determine dynamic values like the username or the actual timestamp.
The default clause affects those INSERT and MERGE commands, which do not specify the column. In our example database theperson table has the columnweight with the default value 0. If we omit this column in an INSERT command, the DBMS will store the value 0.
-- This INSERT command omits the 'weight' column. Therefore the value '0' (which is different from-- the NULL value) is stored in the weight column.INSERTINTOperson(id,firstname,lastname,date_of_birth,place_of_birth,ssn)VALUES(11,'Larry','Goldstein',date'1970-11-20','Dallas','078-05-1120');COMMIT;-- This SELECT retrieves the row ...SELECT*FROMpersonWHEREid=11ANDweight=0;-- ... but not this one:SELECT*FROMpersonWHEREid=11ANDweightISNULL;
Theidentity specification serves for the generation of a series of unique values that act as the Primary Key to the table's rows. The standard defines the syntax as: "GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY". Unfortunately, most DBMS vendors do not support this formulation. Instead, they offer different syntaxes and even different concepts to generate primary key values. Some use a combination of generators/sequences and triggers, others a special data type, or different keywords.
An overview about the wide spread of implementations is available in the wikibookSQL Dialects Reference: Auto-increment_column.
The column constraint clause specifies conditions which all values must meet. There are different column constraint types:
TheNOT NULL phrase defines, that it is not allowed to store the NULL value in the column.
-- The column col_1 is per definition not allowed to hold the NULL valueCREATETABLEt1(col_1DECIMALNOTNULL);-- This INSERT command will failINSERTINTOt1(col_1)values(NULL);-- The same applies to the following UPDATE commandINSERTINTOt1(col_1)values(5);UPDATEt1SETcol_1=NULL;
ThePRIMARY KEY phrase defines that the column acts as the Primary Key of the table. This implies that the column is not allowed to store a NULL value and that the values of all rows are distinct from each other.
CREATETABLEt2(col_1DECIMALPRIMARYKEY);-- This INSERT will fail because a primary key column is not allowed to store the NULL value.INSERTINTOt2(col_1)VALUES(NULL);-- This INSERT worksINSERTINTOt2(col_1)VALUES(5);-- But the next INSERT will fail, because only one row with the value '5' is allowed.INSERTINTOt2(col_1)VALUES(5);
TheUNIQUE constraint has a similar meaning as the PRIMARY KEY phrase. But there are two slight differences.
First, the values of different rows of a UNIQUE column are not allowed to be equal, which is the same as with PK. But they are allowed to hold the NULL value, which is different from Primary Key. The existence of NULL values has an implication. As the termnull = null never evaluates totrue (it evaluates tounknown) there may exist multiple rows with the NULL value in a column which is defined to be UNIQUE.
Second, only one Primary Key definition per table is allowed. In contrast, there may be many UNIQUE constraints (on different columns).
CREATETABLEt3(col_1DECIMALUNIQUE);-- works wellINSERTINTOt3(col_1)VALUES(5);-- fails because there is another row with value 5INSERTINTOt3(col_1)VALUES(5);-- works wellINSERTINTOt3(col_1)VALUES(null);-- works alsoINSERTINTOt3(col_1)VALUES(null);-- check the resultsSELECT*FROMt3;
TheFOREIGN KEY condition defines that the column can hold only those values, which are also stored in a different column of (the same or) another table. This different column has to be UNIQUE or a Primary Key, whereas the values of the foreign key column itself may hold identical values for multiple rows. The consequence is that one cannot create a row with a certain value in this column before there is a row with exactly this certain value in the referred table. In our example database, we have acontact table whose columnperson_id refers to the id of persons. It makes sense that one cannot store contact values before storing the appropriate person.
Foreign Keys are the technique to realize one-to-many (1:m) relationships.
-- A table with a column which refers to the 'id' column of table 'person'CREATETABLEt4(col_1DECIMALREFERENCESperson(id));-- This INSERT works as in table 'person' of our example database there is a row with id = 3.INSERTINTOt4(col_1)VALUES(3);-- This statement will fail because in 'person' there is no row with id = 99.INSERTINTOt4(col_1)VALUES(99);
Column checks inspect the values of the column to see whether they meet the defined criterion. Within such column checks, only the actual column is visible. If a condition covers two or more columns (e.g., col_1 > col_2) a table check must be used.
-- 'col_1' shall contain only values from 1 to 10.-- A hint to MySQL users: MySQL before 8.0.16 accepts the syntax of column checks - but ignores the definitions silently. The same applies to MariaDB before 10.2.1.CREATETABLEt5(col_1DECIMALCHECK(col_1BETWEEN1AND10));-- This INSERT works:INSERTINTOt5(col_1)VALUES(3);-- This statement will fail:INSERTINTOt5(col_1)VALUES(99);
Table constraints define rules which are mandatory for the table as a whole. Their semantic and syntax overlaps partially with the previous showncolumn constraints.
Table constraints are defined after the definition of all columns. The syntax starts with the keyword CONSTRAINT, followed by an optional name. The following example includes the optional namest6_pk,t6_ik andt6_fk. It is a good practice to include names. In the case of an error exception, most DBMS will include this name as part of related error messages - if a name isn't defined, the DBMS may use its internal naming convention, which can be cryptic.
In the same manner as shown in thecolumn constraints part Primary Key, UNIQUE and Foreign Key conditions can be expressed as table constraints. The syntax differs slightly from the column constraint syntax; the semantic is identical.
-- A table with a PK column, one UNIQUE column and a FK column.CREATETABLEt6(col_1DECIMAL,col_2CHAR(10),col_3DECIMAL,CONSTRAINTt6_pkPRIMARYKEY(col_1),-- 't6_pk' is the name of the constraintCONSTRAINTt6_ukUNIQUE(col_2),CONSTRAINTt6_fkFOREIGNKEY(col_3)REFERENCESperson(id));
Similar tocolumn constraints part NOT NULL conditions and simple column checks can be expressed as table expressions.
CREATETABLEt7(col_1DECIMAL,col_2DECIMAL,CONSTRAINTt7_col_1_nnCHECK(col_1ISNOTNULL),CONSTRAINTt7_col_2_checkCHECK(col_2BETWEEN1and10));
If a condition affects more than one column, it must be expressed as a table constraint.
CREATETABLEt8(col_1DECIMAL,col_2DECIMAL,col_3DECIMAL,col_4DECIMAL,-- col_1 can hold only those values which are greater than col_2CONSTRAINTt8_check_1CHECK(col_1>col_2),-- If col_3 is NULL, col_4 must be NULL alsoCONSTRAINTt8_check_2CHECK((col_3ISNULLANDcol_4ISNULL)OR(col_3ISNOTNULLANDcol_4ISNOTNULL)));-- These two INSERTs work as they meet all conditionsINSERTINTOt8VALUES(1,0,null,null);INSERTINTOt8VALUES(2,0,5,5);-- Again: MySQL ignores check conditions silently-- This INSERT fails because col_1 is not greater than col_2INSERTINTOt8VALUES(3,6,null,null);-- This INSERT fails because col_3 is not null and col_4 is nullINSERTINTOt8VALUES(4,0,5,null);
As you have seen, some constraints may be defined as part of the column definition, which is called acolumn constraint, or as a separatetable constraint. Table constraints have two advantages. First, they are a little bit more powerful.
Second, they do have their own name! This helps to understand system messages. Furthermore, it opens the possibility to manage constraints after the table exists and contains data. The ALTER TABLE statement can deactivate, activate, or delete constraints. To do so, you have to know their name.
DROPTABLEt1;DROPTABLEt2;DROPTABLEt3;DROPTABLEt4;DROPTABLEt5;DROPTABLEt6;DROPTABLEt7;DROPTABLEt8;
Create a table 'company' with columns 'id' (numeric, primary key), 'name' (strings of variable size up to 200), 'isin' (strings of length 12, not nullable, unique values).
Create a solution with column constraints only and another one with table constraints only.
-- column constraints onlyCREATETABLEcompany_1(idDECIMALPRIMARYKEY,nameVARCHAR(200),isinCHAR(12)NOTNULLUNIQUE);-- table constraints onlyCREATETABLEcompany_2(idDECIMAL,nameVARCHAR(200),isinCHAR(5),CONSTRAINTcompany_2_pkPRIMARYKEY(id),CONSTRAINTcompany_2_ukUNIQUE(isin),CONSTRAINTcompany_2_check_isinCHECK(isinISNOTNULL));
Create a table 'accessory' with columns 'id' (numeric, primary key), 'name' (strings of variable size up to 200, unique), 'hobby_id' (decimal, not nullable, foreign key to column 'id' of table 'hobby').
Create a solution with column constraints only and another one with table constraints only.
-- column constraints onlyCREATETABLEaccessory_1(idDECIMALPRIMARYKEY,nameVARCHAR(200)UNIQUE,hobby_idDECIMALNOTNULLREFERENCEShobby(id));-- table constraints onlyCREATETABLEaccessory_2(idDECIMAL,nameVARCHAR(200),hobby_idDECIMAL,CONSTRAINTaccessory_2_pkPRIMARYKEY(id),CONSTRAINTaccessory_2_ukUNIQUE(name),CONSTRAINTaccessory_2_check_1CHECK(hobby_idISNOTNULL),CONSTRAINTaccessory_2_fkFOREIGNKEY(hobby_id)REFERENCEShobby(id));-- Test some legal and illegal valuesINSERTINTOaccessory_1VALUES(1,'Fishing-rod',2);COMMIT;-- ...
| COMMIT and ROLLBACK | Structured Query Language Create Table | Data Types |