Re: Error Code: 1146 when creating new table desc

[Peter Brawley]

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.