gistfile1.sql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
CREATE SCHEMA IF NOT EXISTS `bproject` ;

-- -----------------------------------------------------
-- Table `bproject`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bproject`.`users` (
  `UID` INT(25) NOT NULL ,
  `username` VARCHAR(60) NOT NULL ,
  `password` VARCHAR(100) NOT NULL ,
  `ownerFN` VARCHAR(50) NOT NULL ,
  `ownerLN` VARCHAR(100) NOT NULL ,
  `userstatus` TINYINT(1) NOT NULL ,
  `userregistertime` DATETIME NOT NULL ,
  `userlastonline` DATETIME NOT NULL ,
  PRIMARY KEY (`UID`) ,
  UNIQUE INDEX `username` (`username` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bproject`.`company`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bproject`.`company` (
  `CID` INT(25) NOT NULL AUTO_INCREMENT ,
  `ownername` VARCHAR(60) NOT NULL ,
  `companyname` VARCHAR(60) NOT NULL ,
  `companyaddress` VARCHAR(255) NOT NULL ,
  `companyemail` VARCHAR(255) NULL DEFAULT NULL ,
  `companyphone` INT(20) NOT NULL ,
  `companyimage` VARCHAR(255) NULL DEFAULT NULL ,
  `partnership` TINYINT(1) NOT NULL DEFAULT '0' ,
  `type` TINYINT(1) NOT NULL DEFAULT '0' ,
  `introduction` TEXT NULL DEFAULT NULL ,
  `announcement` TEXT NULL DEFAULT NULL ,
  `template` TEXT NULL DEFAULT NULL ,
  PRIMARY KEY (`CID`) ,
  INDEX `ownername` (`ownername` ASC) ,
  UNIQUE INDEX `companyname` (`companyname` ASC) ,
  CONSTRAINT `ownername`
    FOREIGN KEY (`ownername` )
    REFERENCES `bproject`.`users` (`username` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bproject`.`content`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bproject`.`content` (
  `PID` INT(25) NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(60) NOT NULL ,
  `introduction` TEXT NULL DEFAULT NULL ,
  `announcement` TEXT NULL DEFAULT NULL ,
  `template` TEXT NULL DEFAULT NULL ,
  PRIMARY KEY (`PID`) ,
  INDEX `username` (`username` ASC) ,
  CONSTRAINT `username`
    FOREIGN KEY (`username` )
    REFERENCES `bproject`.`company` (`ownername` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bproject`.`product`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bproject`.`product` (
  `RID` INT(25) NOT NULL AUTO_INCREMENT ,
  `companyname` VARCHAR(60) NOT NULL ,
  `productname` VARCHAR(60) NOT NULL ,
  `what` TEXT NOT NULL ,
  `image` VARCHAR(255) NOT NULL ,
  `price` DECIMAL(9,2) NOT NULL ,
  `tag` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`RID`) ,
  INDEX `companyname` (`companyname` ASC) ,
  CONSTRAINT `companyname`
    FOREIGN KEY (`companyname` )
    REFERENCES `bproject`.`company` (`companyname` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bproject`.`comments`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bproject`.`comments` (
  `OID` INT(25) NOT NULL AUTO_INCREMENT ,
  `companyname` VARCHAR(60) NOT NULL ,
  `guestemail` VARCHAR(255) NOT NULL ,
  `comment` TEXT NOT NULL ,
  PRIMARY KEY (`OID`) ,
  INDEX `companyname` (`companyname` ASC) ,
  CONSTRAINT `companyname`
    FOREIGN KEY (`companyname` )
    REFERENCES `bproject`.`company` (`companyname` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

gistfile2.txt