CODEWITHSUNDEEP
mysqlalter
user3205128
user3205128

Reputation: 1

Alter MySQL table with foreign key constraint

There are two tables news and language. This is the original table in MySQL :

CREATE TABLE `news` (
`news_id` int(11) NOT NULL AUTO_INCREMENT,
`fleet_id` int(11) NOT NULL,
`channel_id` int(11) NOT NULL,
`effective_from` date NOT NULL,
`effective_to` date NOT NULL,
`news` text,
PRIMARY KEY (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `language` (
`ietf_language_tag` varchar(5) NOT NULL,
`endonym` varchar(32) NOT NULL,
`exonym` varchar(32) NOT NULL,
 PRIMARY KEY (`ietf_language_tag`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* Issue alter table command */

ALTER TABLE news
ADD ietf_language_tag varchar(5) NOT NULL,
ADD KEY (ietf_language_tag) ,
ADD CONSTRAINT news_fkey1 FOREIGN KEY (ietf_language_tag) REFERENCES   
language(ietf_language_tag) ON DELETE NO ACTION  ON UPDATE NO ACTION ;

/* getting error */

Error:

14:57:51 ALTER TABLE news ADD ietf_language_tag varchar(5) NOT NULL, ADD KEY
(ietf_language_tag) , ADD CONSTRAINT news_fkey1 FOREIGN KEY (ietf_language_tag)
REFERENCES language(ietf_language_tag) ON DELETE NO ACTION ON UPDATE NO ACTION
Error Code: 1215. Cannot add foreign key constraint 0.437 sec

Upvotes: 0

Views: 679

Answers (1)

Jason Heo
Jason Heo

Reputation: 10236

character set issue

Both news and language have different character set latin1 for news, utf8 for language.

FOREIGN_KEY_CHECKS

Even though character sets are same, you will get an error if you have records on news table as follows.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db_name.#sql-296_72a7a, CONSTRAINT news_fkey1 FOREIGN KEY (ietf_language_tag) REFERENCES language (ietf_language_tag) ON DELETE NO ACTION ON UPDATE NO ACTION)

this problem can be fixed with SET FOREIGN_KEY_CHECKS = 0;

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE news
    -> ADD ietf_language_tag varchar(5) NOT NULL,
    -> ADD KEY (ietf_language_tag) ,
    -> ADD CONSTRAINT news_fkey1 FOREIGN KEY (ietf_language_tag) REFERENCES
    -> language(ietf_language_tag) ON DELETE NO ACTION  ON UPDATE NO ACTION ;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Upvotes: 1

Related Questions