Oracle Database MySQL 5.6 Developer — Question 21

You attempt to create two new tables:
CREATE TABLE warehouse (
id int (11) NOT NULL AUTO_INCREMENT,
name varchar (20) NOT NULL,
phone varchar (20) NOT NULL,
PRIMARY KEY ( id)
) ENGINE=MyISAM
CREATE TABLE warehouseitem (
warehouse_id bigint (11) NOT NULL,
item_id int (11) NOT NULL,
count int(11) NOT NULL DEFAULT 0,
KEY "warehouse_id’ (‘warehouse-id) ,
FOREIGN KEY (warehouse_id) REFFERENCES warehouse (id)
) ENGINE= InnoDB
You get this error :
ERROR 1215 ( HYooo): cannot add foreign key constraint
Which two changes are required to permit these statements to execute without any error?

Answer options

• A. The ‘warehouseitem’ table must be managed by the MySAm storage engine.
• B. The ‘warehouse-table must be managed by the InnoDB storage engine.
• C. The foreign key clause must be reversed: FOREIGN KEY warehouse(1)REFERENCES (warehouse-id).
• D. The data types of the ‘warehouse’.’id’ and ‘ warehouseitem.warehouse_is columns must match.
• E. The warehouse_id’ column must be renamed ‘id’ to match the definition on the ‘warehouse’ table.
• F. A UNIQUE key must be defined for the columns (‘item_id’,’warehouse_id’).

Correct answer: C, D

Explanation

The correct answer is C and D because the foreign key constraint requires that the data types of the columns match, which is addressed in D. Additionally, the foreign key definition needs to be corrected, as indicated in C. The other options are incorrect as they do not address the root cause of the foreign key constraint failure.