Member Avatar for sibymary

hi,
here i was trying to create table with primary key and foreign key.
i had used follwing codings...but getting error like" Can't create table 'test.book' (errno: 150)"..thanku 4 any help

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html><head><title>Create Table</title></head>
<body>
<?
/* Change next two lines */

$db="test";
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Select DB Error: ".mysql_error());
/* create table */
mysql_query(
"CREATE TABLE book(
bookid SMALLINT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(bookid),
author VARCHAR(30),
title  VARCHAR(30),
accessno INT(20),edition INT(20),publisher VARCHAR(30),
copies  INT(10),
status VARCHAR(30),CONSTRAINT fkbook FOREIGN KEY (status)
REFERENCES issue(status))") or die(mysql_error());
mysql_query(
"CREATE TABLE issue(
bookid SMALLINT(10),
author VARCHAR(30),title  VARCHAR(30),
accessno INT(20),name VARCHAR(20),regno VARCHAR(30),
date  DATE,rdate  DATE NOT NULL,
status VARCHAR(30) NOT NULL,CONSTRAINT pkissue PRIMARY KEY (rdate,status))") or die(mysql_error());
  • 3 Contributors
  • 7 Replies
  • 137 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Raj sahaniya
Member Avatar for broj1

My guess: make sure that table book does not already exist. Add IF NOT EXISTS to the query.

CREATE TABLE IF NOT EXISTS book(
bookid SMALLINT(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(bookid),
author VARCHAR(30),
title VARCHAR(30),
accessno INT(20),edition INT(20),publisher VARCHAR(30),
copies INT(10),
status VARCHAR(30),CONSTRAINT fkbook FOREIGN KEY (status)
REFERENCES issue(status))
Edited by broj1 because: n/a
Member Avatar for sibymary

i got same error...
with out foreign key,table is created.bt with fk,it is not

Member Avatar for sibymary

after deleting the foreign key of table book ,table is created in mysql...but i want to set foreign key in table book...for that wat should i do

Member Avatar for broj1

I managed to run above code without errors (both tables were created). If this helps you: my MySql server version is 5.1.46, both tables were created by default as MyISAM type.Maybe you find useful info on foreign keys here:

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html. Maybe tables should be of InnoDb type. Just a guess.

Edited by broj1 because: n/a
Member Avatar for Raj sahaniya

Reply: From Raj sahaniya, Dear Sir actually you have not defined the correct syntax for mysql_connect("localhost","user_id","password"), at first you correct this syntax you will able to create your table without any problem like as follows :-

$link = mysql_connect("localhost","root","vertrigo");

Member Avatar for sibymary

hi raj sahaniya,
there is no problem with connection.also i have 1 doubt ,i cant set date as a primary key.it shows some errors...like duplicate primary key

Member Avatar for Raj sahaniya

Because Sir, Primary key follows a rule is that it will not allowed to add primary key to multiple table, that's why its showing error of duplicate primary key on date column and for previous problem i checked your code then i found that there was problem with your database connection otherwise there was no problem i had created after accurate db connection code

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.