Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Unanswered: all 3 fields as one primary key...

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Use VARCHAR instead of TINYTEXT.

  3. #3
    Join Date
    Feb 2009
    Posts
    5
    Quote Originally Posted by shammat
    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

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •