CODEWITHSUNDEEP
phpmysql
shahidfoy
shahidfoy

Reputation: 2301

How to create a proper mySQL CREATE TABLE statement with PHP

I am trying insert this query using PHP into the mySQL database but I keep getting this error:

Can't create table ecommerce1.orders (errno: 150 "Foreign key constraint is incorrectly formed")

How would I form this statement correctly?

$query = "CREATE TABLE `orders` (
`id` INT 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 `date_created` (`date_created` ASC),
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";

echo "<p>************</p>";
echo $query;
echo "<p>************</p>";

if($mysqli->query($query) === TRUE) {
echo "<p>Database table orders created</p>";
}
else {
echo "<p>Error: </p>" . mysqli_error($mysqli);
}

Upvotes: 1

Views: 58

Answers (2)

Basant Rules
Basant Rules

Reputation: 807

Try This One:

CREATE TABLE `users`(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE `orders` (
`id` INT 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 `date_created` (`date_created` ASC),
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 char SET utf8;

Upvotes: 0

Rahul
Rahul

Reputation: 77866

That can't be the case. If you are getting this error that meant somehow both the key column doesn't match by definition (most probably datatype mismatch). If I try with below example code it works fine. See this fiddle for demo http://sqlfiddle.com/#!9/6a15c

CREATE TABLE `users`(`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE `orders` (
`id` INT 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 `date_created` (`date_created` ASC),
INDEX `transaction_id` (`transaction_id` ASC),
CONSTRAINT `fk_orders_users1` FOREIGN KEY (`id`)
  REFERENCES `users` (`id`)
  ON DELETE NO ACTION ON UPDATE NO ACTION

Upvotes: 1

Related Questions