Can someone help me to understand this error and how I can correct it?
#1005 - Can't create table 'ecommerce1.pages' (errno: 150)
supports transactions, row_level_locking and foreign keys.
I am fairly new to php and very inexperienced at creating tables in php MyAdmin.
I am trying to create some tables in the sql query box that I've seen in a book, (Effortless E-Commerce with PHP and MySQL ,Authored by Larry Ullman).
Quote from book “The InnoDB table type will be used consistently, which is the current default storage engine for MySQL.”
I think I am getting this error because of what I read in the MySQL documentation. There are REFERENCES in both queries below.
Referential Actions
Referential actions for foreign keys of InnoDB tables are subject to the following conditions:
While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE
and ALTER TABLE statements using this clause are not allowed for InnoDB tables.
If the above is the reason is why I get this error do I exclude the all the foreign key lines and perform additional actions after the creation of the table? If so what actions do I perform? As I understand the error message foreign keys are supported but can't be added at time of creation.
When looking at this sample for foreign key restraints the syntax and paramaters seem correct. I just don't know about the symbol.
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
CREATE TABLE pages (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
categories_id SMALLINT UNSIGNED NOT NULL,
title VARCHAR (100) NOT NULL,
description TINYTEXT NOT NULL,
content LONGTEXT NULL,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX date_created (date_created ASC),
INDEX fk_pages_categories_idx (categories_id ASC),
CONSTRAINT fk_pages_categories FOREIGN KEY (categories_id)
REFERENCES categories (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE orders (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
users_id INT UNSIGNED NOT NULL,
transaction_id VARCHAR (45) NOT NULL,
payment_status VARCHAR (45) NOT NULL,
payment_amount INT UNSIGNED NOT NULL,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX transaction_id (transaction_id ASC),
CONSTRAINT fk_orders_users1 FOREIGN KEY (id)
REFERENCES users (id)
ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB DEFAULT CHARSET=utf8;
Any assistance will be greatly appreciated.
