#  show innodb status;

#  110111 14:47:12 Error in foreign key constraint of table SomeDatabase/Company:
#  FOREIGN KEY (State_ID) REFERENCES State(State_ID),
#  PRIMARY KEY(Company_ID)
#  ) ENGINE=INNODB:
#  Cannot resolve table name close to:
#  (State_ID),
#  PRIMARY KEY(Company_ID)
#  ) ENGINE=INNODB


use SomeDatabase;

CREATE TABLE Company (
Company_ID INT NOT NULL AUTO_INCREMENT,
Company_Name VARCHAR(100),
Company_Address1 VARCHAR(50),
Company_Address2 VARCHAR(50),
Company_ZIP CHAR(7),
State_ID CHAR(2),
Company_Phone CHAR(10),
Company_Email CHAR(50),
Company_URL CHAR(50),
Provider_ID INT,
INDEX StateIndex (State_ID),
FOREIGN KEY (State_ID) REFERENCES State(State_ID),
PRIMARY KEY(Company_ID)
) ENGINE=INNODB;

CREATE TABLE State (
State_ID CHAR(2) NOT NULL,
State_Name VARCHAR(20),
PRIMARY KEY(State_ID)
) ENGINE=INNODB;

INSERT INTO State VALUES
('AL','Alabama'), ('AK','Alaska'), ('AZ','Arizona'), ('AR','Arkansas'),
('CA','California'), ('CO','Colorado'), ('CT','Connecticut'),
('DE','Delaware'), ('DC','District of Columbia'), ('FL','Florida'),
('GA','Georgia'), ('HI','Hawaii'), ('ID','Idaho'), ('IL','Illinois'),
('IN','Indiana'), ('IA','Iowa'), ('KS','Kansas'), ('KY','Kentucky'),
('LA','Louisiana'), ('ME','Maine'), ('MD','Maryland'),
('MA','Massachusetts'), ('MI','Michigan'), ('MN','Minnesota'),
('MS','Mississippi'), ('MO','Missouri'), ('MT','Montana'),
('NE','Nebraska'), ('NV','Nevada'), ('NH','New Hampshire'),
('NJ','New Jersey'), ('NM','New Mexico'), ('NY','New York'),
('NC','North Carolina'), ('ND','North Dakota'), ('OH','Ohio'),
('OK','Oklahoma'), ('OR','Oregon'), ('PA','Pennsylvania'),
('RI','Rhode Island'), ('SC','South Carolina'), ('SD','South Dakota'),
('TN','Tennessee'), ('TX','Texas'), ('UT','Utah'), ('VT','Vermont'),
('VA','Virginia'), ('WA','Washington'), ('WV','West Virginia'),
('WI','Wisconsin'), ('WY','Wyoming');