MySQL Create Table with Unique Constraints

Question

I am just starting with SQL syntax, and am trying to create a table.

Here is my error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT uc_people_2nd UNIQUE (lastName,firstName), ) ENGINE = INNODB' at line 7

And here is my SQL:

CREATE TABLE `people` (
`_id` INT NOT NULL AUTO_INCREMENT,
`lastName` TEXT NOT NULL,
`firstName` TEXT NOT NULL,
`JSON` TEXT NOT NULL,
PRIMARY KEY(_id)
CONSTRAINT uc_people_2nd UNIQUE (lastName,firstName),
) ENGINE = INNODB;

I tried this in NodeDB (which I am developing in), and then PHPMyAdmin.

Answer 1

Fix the comma and make the names varchar():

CREATE TABLE `people` (
`_id` INT NOT NULL AUTO_INCREMENT,
`lastName` varchar(255) NOT NULL,
`firstName` varchar(255) NOT NULL,
`JSON` TEXT NOT NULL,
PRIMARY KEY(_id),
CONSTRAINT uc_people_2nd UNIQUE (lastName, firstName)
) ENGINE = INNODB;

This works on SQL Fiddle.

Note that you don't have to give a unique constraint a name. You can also drop the constraint keyword, so the following works just fine:

UNIQUE (lastName, firstName)

EDIT:

The text data type is described here on the page with other "large-objects". These are special types that are arbitrarily long (think megabytes). They have limits when used in indexes. In particular, they need a length prefix. So, you cannot declare that a text column is unique. Only that they are unique in the first N characters (up to about 1000).

For names, that is way overkill. MySQL supports string types of various sorts. The most useful is varchar(). These are appropriate for a name field. They can be used with indexes easily. And MySQL supports a plethora of functions on them.

In other words, if you do not know what text is, you do not need it. Learn about and use varchar() and char() (or nvarchar() and nchar() if you need national character set support). Forget about text. One day if you need it, you'll rediscover it.