create database blog;
create table blog_user
(
user_name char(15) not null check(user_name !=''),
user_password char(15) not null,
user_emial varchar(20) not null unique,
primary key(user_name)
)engine=innodb default charset=utf8 auto_increment=1;
create table blog_category
(
category_name char(18) not null check(category_name!=''),
category_date datetime not null,
primary key(category_name)
)engine=innod default charset=utf8 auto_increment=1;
create table blog_article
(
article_id int unsigned not null auto_increment,
article_title varchar(20) not null unique,
article_content longtext not null,
article_date datetime not null,
article_readtime int unsigned not null default 0,
user_name char(15) not null,
category_name char(18) not null,
primary key(article_id),
foreign key(user_name) references blog_user(user_name) on delete cascade on update cascade,
foreign key(category_name) references blog_category(category_name) on delete cascade on update cascade
)engine=innodb default charset=utf8 auto_increment=1;
create table blog_comment (
comment_id int(10) unsigned not null auto_increment,
comment_content varchar(90) not null,
comment_date datetime not null,
article_id int(10) unsigned not null,
user_name char(15) not null,
primary key (comment_id),
foreign key(article_id) references blog_article(article_id) on delete cascade on update cascade,
foreign key(user_name) references blog_user(user_name) on delete cascade on update cascade
)engine=innodb default charset=utf8 auto_increment=1;
(脚本学堂 www.jbxue.com)
create table blog_photoalbum
(
photoalbum_name char(20) not null check(photoalbum_name!=''),
photoalbum_date datetime not null,
primary key(photoalbum_name)
)engine=innodb default charset=utf8;
create table blog_photograph
(
photograph_name varchar(20) not null check(photograph_name!=''),
photograph_date datetime not null,
photoalbum_name char(20) not null,
photourl varchar(90) not null,
foreign key(photoalbum_name) references blog_photoalbum(photoalbum_name) on delete cascade on update cascade
)engine=innodb default charset=utf8;