I ran this using MySql and it appears to not like TEXT. With SQL server I use nvarchar(max) What should I use in MySql? In other tables some fields will be descriptions and may be long so at the moment I am thinking that fixed length is bad.
create table if not exists
misc_info (
id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
key TEXT UNIQUE NOT NULL,
value TEXT NOT NULL
)ENGINE=INNODB;
You can't have a UNIQUE index on a text column in MySQL.
If you want to index on a TEXT or a BLOB field, you must specify a fixed length to do that.
From MySQL documentation:
BLOB and TEXT columns also can be indexed, but a prefix length must be given.
Example:
CREATE UNIQUE INDEX index_name ON misc_info (key(10));
Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns.
I think it chokes on the key field name rather than the TEXT type (which should be perfectly fine).
(And as @Pablo already said, memo fields can't be unique.)
Two things:
UNIQUE(key) TEXT value.
