Here are my table create statements, I’m sure it’s something simple but I can’t figure it out. I’m using Mysql version 5.1.44
CREATE TABLE IF NOT EXISTS `Poll` (
`poll_id` int(11) NOT NULL AUTO_INCREMENT,
`poll_description` varchar(255) NOT NULL,
`poll_result` varchar(20) DEFAULT NULL,
`room_name` varchar(20) NOT NULL,
`before_settings` varchar(20) NOT NULL,
`after_settings` varchar(20) NOT NULL,
`in_session` tinyint(4) NOT NULL,
`started` bigint(20) DEFAULT NULL,
`finished` bigint(20) DEFAULT NULL,
`started_by` varchar(20) DEFAULT NULL,
`room_id` int(11) NOT NULL,
PRIMARY KEY (`poll_id`),
FOREIGN KEY (`room_name`) REFERENCES Room (room_name),
FOREIGN KEY (`room_id`) REFERENCES Room (room_id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS `Room` (
`room_id` int(11) NOT NULL AUTO_INCREMENT,
`room_name` varchar(20) DEFAULT NULL,
`created` bigint(20) NOT NULL,
PRIMARY KEY (`room_id`)
);
So Room has a one to many relationship with Poll, Room being on the one side and Poll is the many. Anyone spot anything obvious? DB stuff is not my strong suit
I’m trying to create table with InnoDB as the engine, not letting me create tables with FK relationships is seems. Keep getting errno 150 , Can’t create table error. I’ve been sifting through some information on that but as of yet can’t really find an clear cut answer. DB configuration is not a strong suit of mine. Don’t suppose you’ve come across this problem?
For example. here’s 2 tables. Room it lets me create, but it won’t let me create user, or any other table referencing room. Keep getting the above error
CREATE TABLE IF NOT EXISTS `Room` (
`room_id` int(11) NOT NULL AUTO_INCREMENT,
`room_name` varchar(20) DEFAULT NULL,
`created` bigint(20) NOT NULL,
PRIMARY KEY (`room_id`)
)ENGINE=InnoDB;
CREATE TABLE User (
user_id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
has_voted INT NOT NULL,
can_vote INT NOT NULL,
room_name VARCHAR(10) NOT NULL,
is_moderator INT NOT NULL,
last_access bigint(20) DEFAULT NULL,
PRIMARY KEY (user_id),
FOREIGN KEY (room_name) REFERENCES Room (room_name)ON DELETE CASCADE)ENGINE=InnoDB;
You are probably going to run into problems with your schema as it is.
Yii requires that you use a unique primary key named ‘id’.
I would create your table like this:
Note I have use datetime for the ‘created’ and ‘last_access’ fields. Change them to bigint if that is what you require.
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';
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- -----------------------------------------------------
-- Table `room`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `room` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`room_name` VARCHAR(20) NOT NULL ,
`created` DATETIME NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(10) NOT NULL ,
`has_voted` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 ,
`can_vote` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 ,
`is_moderator` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 ,
`last_access` DATETIME NULL ,
`room_id` INT(11) UNSIGNED NOT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_user_room` (`room_id` ASC) ,
CONSTRAINT `fk_user_room`
FOREIGN KEY (`room_id` )
REFERENCES `room` (`id` )
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
EDIT:
In fact, as I see it now, you actually need a many_to_many relationship because a user may or may not be associated with a room and could possibly be associated with many rooms at once.
If that is the case I can help you with the new schema but no doubt that is going to cause more confusion to you at this early stage.
Thanks for your input, a user can only be in one room at any one time so it is a one to many, i.e. one room, many users. For the time being I’ve taken care of the delete statements i need in my php code, rather than relying on the Db relationships to do it. It works, not sure if it’s a bad idea for the long run though.
