CODEWITHSUNDEEP
mysqlsql
Nirab Pokharel
Nirab Pokharel

Reputation: 1

I am not able to create the table in SQL when entering multiple foreign keys

Below is my SQL file and the table is not created when I try to insert more than 1 foreign keys in the sql. The error is ""Foreign key constraint is incorrectly formed 

 CREATE TABLE company_domaindetails (
    domain_id int NOT NULL,
    domain_name VARCHAR(200) NOT NULL,
    domain_provider VARCHAR(200) NOT NULL,
    domain_accoemail VARCHAR(200) NOT NULL,
    domain_allopassword VARCHAR(200) NOT NULL,
    domain_dns VARCHAR(200) NOT NULL,
    domain_timeframe VARCHAR(200) NOT NULL,
    domain_daysremaining VARCHAR(200) NOT NULL,
    company_id int,
    company_name varchar(200),
    PRIMARY KEY (domain_id),
    FOREIGN KEY (company_id) REFERENCES company_details(company_id),
    FOREIGN KEY (company_name) REFERENCES company_details(company_id)    
);

From the (now deleted) comments:

CREATE TABLE company_details
(
   company_id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   company_name VARCHAR(200) NOT NULL, 
   company_address VARCHAR(200), 
   company_contactperson VARCHAR(200), 
   company_contactno VARCHAR(200), 
   company_email VARCHAR(200) 
);

Upvotes: 0

Views: 37

Answers (1)

Moudiz
Moudiz

Reputation: 7377

You have to specify once the foreing key creation and add both columns. Check the below

CREATE TABLE company_details ( company_id int NOT NULL, company_name VARCHAR(200) NOT NULL,
 company_address VARCHAR(200), company_contactperson VARCHAR(200),
  company_contactno VARCHAR(200), company_email VARCHAR(200),
 PRIMARY KEY (company_id,company_name) )

As per the DDL you provided and specfyin the table name as company_domaindetails

CREATE TABLE company_domaindetails (
    domain_id int NOT NULL,
    domain_name VARCHAR(200) NOT NULL,
    domain_provider VARCHAR(200) NOT NULL,
    domain_accoemail VARCHAR(200) NOT NULL,
    domain_allopassword VARCHAR(200) NOT NULL,
    domain_dns VARCHAR(200) NOT NULL,
    domain_timeframe VARCHAR(200) NOT NULL,
    domain_daysremaining VARCHAR(200) NOT NULL,
    company_id int not null,
    company_name varchar(200),
    PRIMARY KEY (domain_id,company_name),
    FOREIGN KEY (company_id,company_name) REFERENCES company_details(company_id,company_name)
    )

Upvotes: 1

Related Questions