Do you speak SQL? (Part 2: Creating tables)

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.

 

3 thoughts on “Do you speak SQL? (Part 2: Creating tables)”

  1. All right, so is this something that you would include in the text of a .php page that creates the dictionary on the page? I mean, you could simply pre-prepare the table using PHPMyAdmin, right? Is there a reason you’d want the .php page to try to create this table each time it’s loaded?

  2. No PHP code on this page. It’s all SQL. And yes, you would do this only once. There’s no reason to run this code multiple times. The phpMyAdmin interface does this for you, though you would still have to define datatypes and designate things NOT NULL and so forth. Or, you can have the sql all written up, go to the phpMyAdmin import tab for the database you want to use, and then import the textfile with the SQL statements in it. That will also work. You can also copy and paste the above SQL into phpMyAdmin’s SQL tab for the database as well.

Comments are closed.