GrassField

I am very bad at quering. I was playing with mysql yesterday and find it is very easy to create tables with foreign key relations. see the query

Table1: USER
——————————
user_id* | user_nick
——————————

create table user(
user_id int not null auto_increment,
primary key (user_id),
user_nick varchar(128) not null);

Table1: PROFILE
——————————
profile_id* | profile_nick
——————————
create table profile(
profile_id int not null auto_increment,
primary key(profile_id),
profile_nick varchar(128) not null);

Table1: USER_INFO
————————————————–
user_id*# | user_role# | user_name | user_avatar
————————————————–

create table user_info(
user_id int not null,
primary key (user_id),
index (user_id),
foreign key (user_id)
references user(user_id)
on update cascade
on delete restrict,
user_role int not null,
index (user_role),
foreign key (user_role)
references profile(profile_id)
on update CASCADE
on delete restrict,
user_name varchar(128),
user_avatar varchar(128)
);

Table1: PROFILE_INFO
——————————————————————–
profile_id*# | browse | own_entries | contributor | share | admin |
——————————————————————–
create table profile_info(
profile_id int not null,
primary key(profile_id),
index (profile_id),
foreign key(profile_id)
references profile(profile_id)
on update cascade
on delete restrict,
browse boolean,
own_entries boolean,
contributor boolean,
share boolean,
admin boolean);