• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1245
  • Last Modified:

Error message for binding primary keys

Hi;
I am trying to create relation between 3 tables with the same primary key and this is the error I get.  Can you please help me as I am brand new in mysql and this will be a huge favor ro me.

ERROR 1061: Duplicate key name 'Media_ID'

SQL Statement:

ALTER TABLE `iran79`.`media_owner_info`

ADD INDEX `Media_ID` (`Media_ID` ASC)

, ADD INDEX `Media_ID` (`Media_ID` ASC)



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,

  `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

0
babak62
Asked:
babak62
  • 9
  • 8
1 Solution
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
one name can be used only for once.
to join 3 tables you need two pk, fk constraints with two different names
0
 
babak62Author Commented:
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.
0
 
Jason C. LevineNo oneCommented:
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

Open in new window


Works fine when I run it in a blank database...
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Jason C. LevineNo oneCommented:
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
0
 
babak62Author Commented:
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

0
 
Jason C. LevineNo oneCommented:
>> ERROR 1046: No database selected

Did you in fact, select a database?

How are you doing this?  From the MySQL command line?
0
 
babak62Author Commented:
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.
0
 
Jason C. LevineNo oneCommented:
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.  
0
 
babak62Author Commented:
I executed the same script in phpmyadmin and this is the error I got

#1273 - Unknown collation: 'utf8'
0
 
babak62Author Commented:
one more thing the database is not blank so could that be the problem?
0
 
Jason C. LevineNo oneCommented:
Answer my first questions:

Does media_owner_info already exist?  If so, does it have an auto-increment column defined?
0
 
babak62Author Commented:
Yes it does exist and the auto increment is the primary key also "Media_ID"
0
 
Jason C. LevineNo oneCommented:
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?

0
 
babak62Author Commented:
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.
0
 
Jason C. LevineNo oneCommented:
Can't do that solely through MySQL commands.  That happens via your PHP coding.

When you inset the record into the parent table, you need to use mysql_insert_id() after the insert operation to get the newly created primary key value.  You then use that value to update whatever other tables you need to but you don't insert the value into the primary key of the sub-tables.  Instead, you just insert it into a normal column and that becomes the Foreign Key.

If you are new to PHP and MySQL, then Dreamweaver is not going to be of any great to help to you.  It does not naturally operate like this with it's Insert and Update behaviors.
0
 
babak62Author Commented:
is there any other tool that I can use to do php which is in the same nature as dreamweaver that you suggest?
0
 
babak62Author Commented:
Great Help
0
 
Jason C. LevineNo oneCommented:
>> 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.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now