Hi

I need to create a table that records user's privileges of different files:

This works fine:
CREATE TABLE myTable
(
user_id INT NOT NULL,
file_id INT NOT NULL,
privilege_name TINYTEXT NOT NULL,
PRIMARY KEY (user_id, file_id, privilege_name)
)

However since privilege names could be read and write, and if the user has both read and write privileges, then we need 2 records. So I attempted to use all 3 fields as one primary key as follows:

CREATE TABLE myTable
(
user_id INT NOT NULL,
file_id INT NOT NULL,
privilege_name TINYTEXT NOT NULL,
PRIMARY KEY (user_id, file_id, privilege_name)
)

However now phpmyadmin gives me an error:
#1170 - BLOB/TEXT column 'privilege_name' used in key specification without a key length

Does anyone know how to solve this problem?

Thanks,

Tom

Use VARCHAR instead of TINYTEXT.

Hi Shammat

I used

privilege_name VARCHAR(15) NOT NULL,

and it worked. But could you please explain why TINYINT would not work?

Thank you very much.

Tom

not tinyint but tinytext and it didn't work because when you include a column that is text/blob type you must specify the number of characters in that column that are part of the index.