CODEWITHSUNDEEP
mysql
AndreasSoul
AndreasSoul

Reputation: 13

Unable to create foreign key on mysql

Hi i'm trying to create a simple MySQL database. I write the following script 

CREATE TABLE office (
   num_office varchar(10) NOT NULL,
   name varchar(45),
   address varchar(45),
   numder varchar(4),
   city varchar(45)
 );



CREATE TABLE office_phone(
n_office varchar(10) NOT NULL,
phone varchar(15) NOT NULL,
PRIMARY KEY(n_office,phone),
FOREIGN KEY (n_office) REFERENCES office(num_office)
ON DELETE RESTRICT 
ON UPDATE CASCADE
);

but Error Code 1822 is appeared. How can i fix it? thank you in andvance

Upvotes: 0

Views: 63

Answers (2)

Sowmya Shankark
Sowmya Shankark

Reputation: 71

Reference column (Column you referenced as foreign key) should have the index added to it. You can add Index to the column "num_office" in "office" table and try creating the "office_phone" table. Refer the following queries

Create Table "office" with the index added to "num_office"

CREATE TABLE office (
  num_office varchar(10) NOT NULL,
  name varchar(45),
  address varchar(45),
  numder varchar(4),
  city varchar(45),
  INDEX num_office_idx (num_office)

);

OR you can add index after creating the "office" table using

ALTER TABLE office Add Index num_office_indx (num_office);

This will let you to create the table "office_phone" successfully with the reference key pointing to "num_office" column of "office" table.

CREATE TABLE office_phone(
 n_office varchar(10) NOT NULL,
 phone varchar(15) NOT NULL,
 PRIMARY KEY(n_office,phone),
 FOREIGN KEY (n_office) REFERENCES office(num_office)
 ON DELETE RESTRICT 
 ON UPDATE CASCADE
);

Refer this page for more details

Upvotes: 1

Vasan
Vasan

Reputation: 4956

Foreign keys can reference only columns that are primary keys or at least have an index on them (thanks @Uueerdo). For example:

ALTER TABLE OFFICE ADD CONSTRAINT offnum_unq UNIQUE (NUM_OFFICE);

This forces the referenced column to have unique values and also creates an index on it. Or you could also do:

CREATE INDEX offnum_idx ON OFFICE(NUM_OFFICE);

Now your second CREATE should work.

Upvotes: 1

Related Questions