Error message for binding primary keys

one name can be used only for once.
to join 3 tables you need two pk, fk constraints with two different names

Can you please tell me how to correct the syntax as I have no idea where to do it.  Thanks a lot for the help.

Does the table already exist?  Because this query:

CREATE TABLE `media_owner_info` (
  `Media_ID` int(11) NOT NULL AUTO_INCREMENT,
  `First_Name` varchar(45) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,
  `Last_Name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Email_Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Business Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Job_Title` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Home_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Mobile_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Zip` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `State` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `City` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`Media_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Select allOpen in new window

Works fine when I run it in a blank database...

If you are running:

ALTER TABLE `iran79`.`media_owner_info`
ADD INDEX `Media_ID` (`Media_ID` ASC),
ADD INDEX `Media_ID` (`Media_ID` ASC)

On the existing table, there is no need.  There is already an index on the Media_ID column because it is the Primary Key

Thanks for the response this is what I got back


ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `media_owner_info` (

  `Media_ID` int(11) NOT NULL AUTO_INCREMENT,

  `First_Name` varchar(45) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,

  `Last_Name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Email_Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Business Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Job_Title` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Home_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Mobile_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Zip` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `State` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `City` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`Media_ID`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_



ERROR: Error when running failback script. Details follow.



ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `media_owner_info` (

  `Media_ID` int(11) NOT NULL AUTO_INCREMENT,

  `First_Name` varchar(45) CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL,

  `Last_Name` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Email_Address` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Business Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Home_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Mobile_Phone` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Address` varchar(150) CHARACTER SET utf8 DEFAULT NULL,

  `Zip` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `State` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `City` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  `Country` varchar(45) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`Media_ID`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

>> ERROR 1046: No database selected

Did you in fact, select a database?

How are you doing this?  From the MySQL command line?

I went to the workbench, opened the table and under the columns selected the tables, using the drop downs, and assigned the primary key "media_ID" to other tables and tried to apply, then when I got the first error in the sql screen pasted your code and ran it.  Sorry if it does not sound so logical but this is my second day working with these tools.

I'm not real familiar with the Workbench (I don't use it) but something above sounds off.

Does media_owner_info already exist?  If so, does it have an auto-increment column defined?

For the other two tables, you add media_ID as a column but do NOT make it the primary key.  

I executed the same script in phpmyadmin and this is the error I got

#1273 - Unknown collation: 'utf8'

one more thing the database is not blank so could that be the problem?

Answer my first questions:

Does media_owner_info already exist?  If so, does it have an auto-increment column defined?

Yes it does exist and the auto increment is the primary key also "Media_ID"

Okay, so there is no need to run

CREATE TABLE `media_owner_info`

again, nor is there a need to ALTER it to add a new index on Media_ID

So what are you trying to do?

I am trying to update the other tables with the same key when I insert a record to the media_owner_info and insert the same record to other table at the same time.

is there any other tool that I can use to do php which is in the same nature as dreamweaver that you suggest?

Great Help

>> is there any other tool that I can use to do php which is in the same nature as dreamweaver that you
>> suggest?

Kind of.  There is a paid extension for Dreamweaver that does integrate mysql_insert_id() into its behaviors and writes it to a Session Variable automatically.

DataAssist from WebAssist:  http://www.webassist.com/dreamweaver-extensions/dataassist/

However, I would urge you to spend a fair amount of time reading up on how to handle INNER JOINS in MySQL and how to design a complete database before throwing yourself into the code for this.