Most SQL tutorials start with SELECT statements and then go into all sorts of detail about the various functions and elaborations available. These are really cool, and I will go into them eventually, but I have found that aside from basic syntax and a handful of functions most conlangers do not need to know all that in order to display a dictionary entry or list of entries. So I am going to skip all that for now and talk about creating tables.
The SQL for creating tables starts with an obvious CREATE TABLE followed by the name of the table and then a list of the column names with descriptive material in parentheses. Like so:
CREATE TABLE IF NOT EXISTS `definitions` ( `definitionID` INT(11) NOT NULL AUTO_INCREMENT, `wordID` INT(11) NOT NULL, `definition_number` INT(2) NOT NULL DEFAULT '1', `part_of_speech` VARCHAR(25) COLLATE utf8_general_ci DEFAULT NULL, `definition_text` TEXT COLLATE utf8_general_ci, PRIMARY KEY (`definitionID`), CONSTRAINT `fk_defword` FOREIGN KEY (`wordID`) REFERENCES `dictionary_word` (`wordID`) );
The statement above creates the table named “definitions”. The phrase IF NOT EXISTS means that if a table by that name already exists, then nothing happens. If a table by that name does not exist, then the table is created with 5 columns, a primary key, and a foreign key constraint.
Let’s take this column by column. The first column created is called “definitionID”, it is described as an integer of 11 places maximum. (That’s the default for MySQL’s INT datatype.) It cannot be NULL, and the value is automatically incremented every time an entry is added to this table.
The next column is “wordID”, which is also an integer of 11 places maximum and also cannot be null. The third column is “definition_number”, which is an integer of 2 places maximum. That means its value cannot go above “99”. It also has a default value of “1”.
The fourth column “part_of_speech” has a description of VARCHAR(25). CHAR and VARCHAR are common datatypes for strings. CHAR has a fixed width, and VARCHAR has a variable width. In this column, VARCHAR’s maximum width or number of characters is 25. This column also has a defined collation of utf8_general_ci and a default of NULL. Actually, DEFAULT NULL is optional, since NULL is already the default of any column not designated as NOT NULL. Collations I will discuss some other time.
The fifth column “definition_text” has a datatype of TEXT. In many ways TEXT is simply VARCHAR without a maximum size. It used to be (MySQL version 5.0.3 and earlier) that VARCHAR was limited to a maximum size of 255 characters while TEXT could store up to 65536 bytes*. TEXT types have no stated size or default values.
After that comes the PRIMARY KEY definition. Each table ought to have a primary key, and it is best that the primary key is not derived from the data in the other columns. And auto-incremented integer is the usual form of a primary key, but the only requirements are that all values be unique and never null. Defining a primary key tells the database how to index the data in the table so it can be found in a query. Here we have declared “definitionID” to be the primary key.
The foreign key constraint (here arbitrarily named “fk_defword”) tells the database that one (or more, sometimes) of the columns in the table references a key (usually the primary key) in another table. This is how one connects data in one table to data in another table. Here the column “wordID” references the key “wordID” in the “dictionary_word” table.
Here’s another create table statement for you. If you have any questions, ask them in the comments.
CREATE TABLE `dictionary_word` ( `wordID` int(11) NOT NULL AUTO_INCREMENT, `word` varchar(45) NOT NULL, `pronunciation` varchar(45) DEFAULT NULL, `etymology` text, PRIMARY KEY (`wordID`) );
*but only if a TEXT column is the only thing in the table. Otherwise the maximum size of a TEXT column is 65536 bytes minus the number of bytes needed for all the other columns in the table.