MySQL Multiple Column Indexes
Posted 11 June 2004 - 09:41 PM
I'm currently using PHP to ensure that the record doesn't exist before inserting it, but I'd love to have the database coded properly to ensure this doesn't happen.
Posted 12 June 2004 - 06:50 PM
can your DB-System no "primary key"?
In your group-table the group-id and the user-id should be the primary key!
This protects a duplicate tuple. If i understand you right, is it what you will.
Further, you should check, if you can use a "foreign-key". This means the user-id can only inserted if the user-id exists in the user-table.
What DB-System is used ?
Posted 30 June 2004 - 09:29 AM
I'm using MySQL. I would like to code it to work with version 3.23.44 but if I need to use 4.0.1 or higher that is fine too.
In order to use Foriegn Keys I guess I would need to use InnoDB tables. Right now my tables are MyISAM. But the benifits of checking with the keys in other tables would probably pay off in the long run. Both the user_id and group_id would be foreign keys.
Could you give me a SQL code example of how to define the keys for the tables?
I don't quite understand how to define them. For example lets say there are two columns A & B which need indexing. How would I define two separate unique indexes for each column? How would I define a single unique index for the combination of the two columns?
Posted 31 August 2004 - 09:50 PM
create table groups(id int(12) not null, uid, int (12) not null, group int(12) not null, primary key(id), key users(u_id));
Then, whenever you insert into the table, you would provide three values
id = $uid . $grp_id
uid = $uid
group = $group
And there you have it. I hope that helps
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users