problem setting up primary keys in mysql database... help?

I have a table set up on a mysql database called "access" with three columns called:

rights_id, (PRIMARY KEY)

username,

name,

In the rights_id column the user can only input 3 different values ("1","2", or "3") 开发者_如何转开发1 means resource, 2 means manager, and 3 means administrator. my problem occurs when there are more than one row with the same rights_id (ie: more than one administrator).It displays an error that tells me i can't have a duplicate entry for the PRIMARY KEY... i was wondering if there was a way to supress this error and allow me to do this? im using vb.net to interact with my MYSQL database running on a Windows 7 OS. Thanks!

rights_id is primary key. You can have only distinct values of primary keys in a table. So consider another primary key or do not use rights_id column this way. You should learn more about relational databases if you would like to use them.

In my opinion the best solution there is to add anothe column id which could be a primary key (you could also set multi-column primary key but this wouldn't fit your data in my opinion).

I'm not sure what "name" means in that table. If it's safe for me to ignore it . . .

If each username can have only one "rights_id", then the primary key should be username. If each username can have more than one "rights_id"--if user Catcall can have rights_id 1 and 2 at the same time--then your primary key should be the pair of columns (rights_id, username).

Since MySQL doesn't enforce CHECK constraints, you should have a separate table of rights id numbers, containing three rows.

create table rights_ids (
  rights_id integer primary key
);
insert into rights_ids values (1);
insert into rights_ids values (2);
insert into rights_ids values (3);

Then you can set a foreign key constraint that will prevent any numbers besides those three from appearing in the table named "access". Something like

alter table access
add constraint foreign key (rights_id) references rights_ids (rights_id);

Create a compound PRIMARY KEY of rights_id and username (if usernames are unique that is).

No, you can't suppress that error. The issue is that rights_id is NOT your primary key.

The primary key must be able to uniquely identify a row in your table. If you can have more than 1 rights_id entry, then that is NOT able to fulfill the role of a primary key.

Read this wiki article about unique keys (a primary key is a specific type of unique key).

As Shef pointed out, you'll likely want to use a compound primary key of rights_id and username if that combination actually uniquely identifies a single row in the table.