-1
DROP DATABASE IF EXISTS steelers;
CREATE DATABASE steelers;

-- select the database
USE steelers; 
   
-- Create the table 'defense'       
CREATE TABLE defense (
  defense_id int(11) NOT NULL AUTO_INCREMENT,
  player_position varchar(5) DEFAULT NULL,
  player_jersey_number int(11) DEFAULT NULL,
  player_first_name varchar(45) DEFAULT NULL,
  player_last_name varchar(45) DEFAULT NULL,
  player_id int(11)  DEFAULT NULL,
PRIMARY KEY (defense_id),
KEY player_id_idx (player_id),
CONSTRAINT c_id 
FOREIGN KEY (player_id) 
REFERENCES players (player_id) 
ON DELETE NO ACTION ON UPDATE NO ACTION)
ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;

CREATE TABLE players (
  player_id int(11) NOT NULL AUTO_INCREMENT,
  player_jersey_number int(11) DEFAULT NULL,
  player_first_name varchar(45) DEFAULT NULL,
  player_last_name varchar(45) DEFAULT NULL,
  player_position varchar(5) DEFAULT NULL,
  player_age int(11) DEFAULT NULL,
  player_weight int(11) DEFAULT NULL,
  player_college varchar(45) DEFAULT NULL,
  player_height int(11) DEFAULT NULL,
  player_experience int(11) DEFAULT NULL,
  PRIMARY KEY (player_id))
  ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;

Error Code: 1215. Cannot add foreign key constraint
philipxy
  • 14,867
  • 6
  • 39
  • 83
user17564246
  • 1

1 Answers1

0

Create table players first. Then create the table defense that references players. The order in which you create the tables is important.

Alternatively, create all tables in any order, then ALTER TABLE to add foreign key constraints as needed.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828