#1072 - Key column 'Customer_ID' doesn't exist in table

user_8999787 0
Viewed 4798

This is the parent table

CREATE TABLE Customer(
Customer_ID INT(5) not null,
CustName VARCHAR(50) not null,
CustSurname VARCHAR(50) not null,
CustEmail VARCHAR(100) unique not null,
CustMobileNo INT(12) not null,
HomeAddress VARCHAR(255) not null,
Password VARCHAR(10) not null,
constraint c_cuid_pk PRIMARY KEY (Customer_ID))
ENGINE = InnoDB;

When I try to create the property table it shows me

"#1072 - Key column 'Customer_ID' doesn't exist in table"

this is the child table

CREATE TABLE Property(
Property_ID INT(5) not null,
PhouseNumber INT(4) not null,
PstreetName VARCHAR(50) not null,
PpostCode VARCHAR(7) not null,
Pcity VARCHAR(20) not null,
constraint p_phn_pk PRIMARY KEY (Property_ID),
constraint p_cuid_fk FOREIGN KEY (Customer_ID)
references IndividualCustomer(Customer_ID))
ENGINE = InnoDB;
mysql sql database
3 Answers

In table Property, you must define a column named Customer_ID (well it could be named anything!), then you define the FOREIGN KEY on it.

Your code currently tries to link IndificualCustomer(Customer_ID) to Property(Customer_ID), but Property(Customer_ID) does not exist.

Declaring the constraint just links the columns, but it does not create them.

CREATE TABLE Property(
Property_ID INT(5) not null,
PhouseNumber INT(4) not null,
PstreetName VARCHAR(50) not null,
PpostCode VARCHAR(7) not null,
Pcity VARCHAR(20) not null,
Customer_ID INT(5) not null,
constraint p_phn_pk PRIMARY KEY (Property_ID),
constraint p_cuid_fk FOREIGN KEY (Customer_ID)
references IndividualCustomer(Customer_ID))
ENGINE = InnoDB;
user_23209610

In the first place, you should add a column: Customer_ID to your Property table to create your foreign key constraint: 

Property.Customer_ID →　IndividualCustomer.Customer_ID

You can not make a constraint on something that does not exist. Or change the +++ +++ part of your table creation query to a column that exists.

constraint p_cuid_fk FOREIGN KEY (+++Customer_ID+++) --to be changed accordingly
references IndividualCustomer(Customer_ID)

Example: adding the Customer_ID column to your table declaration would give you the following code

CREATE TABLE Property(
Property_ID INT(5) not null,
Customer_ID INT(5) not null,
PhouseNumber INT(4) not null,
PstreetName VARCHAR(50) not null,
PpostCode VARCHAR(7) not null,
Pcity VARCHAR(20) not null,
constraint p_phn_pk PRIMARY KEY (Property_ID),
constraint p_cuid_fk FOREIGN KEY (Customer_ID)
references IndividualCustomer(Customer_ID))
ENGINE = InnoDB;
user_87942210 
CREATE TABLE Property(
Property_ID INT(5) not null,
PhouseNumber INT(4) not null,
PstreetName VARCHAR(50) not null,
PpostCode VARCHAR(7) not null,
Pcity VARCHAR(20) not null,
Customer_ID INT(5) not null,
PRIMARY KEY (Property_ID),
KEY p_cuid_fk (Customer_ID),
constraint p_cuid_fk FOREIGN KEY (Customer_ID)
references Customer(Customer_ID))
ENGINE = InnoDB;

You have to add Customer_ID in the table PROPERTY.

user_87993890
Related