MySQL Forums
Forum List  »  MySQL Workbench

Re: Error Code: 1146 when creating new table desc
Posted by: Peter Brawley
Date: June 26, 2022 07:00PM

You've missed something. Perhaps you forgot to delete the existing copy of `new_table`?

USE CIA_DATA;
DROP TABLE IF EXISTS new_table;
CREATE TABLE new_table (
  Water DECIMAL(3,1) NOT NULL,
  Sanitation DECIMAL(3,1) NOT NULL,
  GDP INT NOT NULL,
  Life DECIMAL(3,1) NOT NULL,
  Underweight DECIMAL(3,1) NOT NULL,
  Literacy DECIMAL(3,1) NOT NULL,
  Electricity DECIMAL(3,1) NOT NULL,
  Country VARCHAR(45) NOT NULL,
  PRIMARY KEY (GDP),
  UNIQUE INDEX Country_UNIQUE(Country ASC) -- REMOVE VISIBLE, NOT NEEDED
) ENGINE = InnoDB ;                        -- TERMINATING SEMICOLON WAS MISSING

INSERT INTO `CIA_DATA`.`new_table`
 /* SELECT */ ( `Water`,                   -- SYNTAX ERROR, NEEDS LEFT PAREN NOT SELECT
`Sanitation`,
`GDP`,
`Life`,
`Underweight`,
`Literacy`,
`Electricity`,
`Country`)
VALUES
(68.5,21.8,600,54.2,24.6,37.4,14,'Central_African_Republic');

COMMIT;

SELECT * FROM new_table;


+-------+------------+-----+------+-------------+----------+-------------+--------------------------+
| Water | Sanitation | GDP | Life | Underweight | Literacy | Electricity | Country                  |
+-------+------------+-----+------+-------------+----------+-------------+--------------------------+
|  68.5 |       21.8 | 600 | 54.2 |        24.6 |     37.4 |        14.0 | Central_African_Republic |
+-------+------------+-----+------+-------------+----------+-------------+--------------------------+

Added note: A sounder design would store 2- or 3-character country codes rather than the full names of countries, which not uncommonly change and therefore belong in a lookup table keyed by such 2- or 3-character codes, see https://dev.mysql.com/doc/world-setup/en/. Consider reading some primers on relational database design.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Error Code: 1146 when creating new table desc
346
June 26, 2022 07:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.