0

The foreign key in the user table seems like don't allow me to input my data, the primary table is the company table.

I try to make companyid as not primary key in the users table but there is not much use 

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `webpage` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `webpage` ;

-- -----------------------------------------------------
-- Table `webpage`.`company`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `webpage`.`company` (
  `idcompany` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `user_id` INT NOT NULL,
  PRIMARY KEY (`idcompany`),
  INDEX `fk_company_user1_idx` (`user_id` ASC),
  CONSTRAINT `fk_company_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `webpage`.`user` (`iduser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `webpage`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `webpage`.`user` (
  `iduser` INT NOT NULL,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `first_name` TEXT NOT NULL,
  `last_name` TEXT NOT NULL,
  `gender` TEXT NOT NULL,
  `company_id` INT NOT NULL,
  PRIMARY KEY (`iduser`),
  INDEX `fk_user_company_idx` (`company_id` ASC),
  CONSTRAINT `fk_user_company`
    FOREIGN KEY (`company_id`)
    REFERENCES `webpage`.`company` (`idcompany`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

This is the error messages that show

Cannot add or update a child row: a foreign key constraint fails (webpage.users, CONSTRAINT fk_users_company1 FOREIGN KEY (company_idcompany) REFERENCES company (idcompany) ON DELETE NO ACTION ON UPDATE NO ACTION)

Dharman
  • 30,962
  • 25
  • 85
  • 135
guess wo am I
  • 13
  • 2
  • 1
    Please include all table definitions. Also, you can probably delete this PHP code, as it is not related to the actual underlying problem. – Tim Biegeleisen May 29 '19 at 05:01
  • the table codes? – guess wo am I May 29 '19 at 05:26
  • 1
    What value of "company_id" are you trying to insert into `webpage.users`? Does that value exist in `webpage.company`? That's probably your problem. – Mike Sherrill 'Cat Recall' May 29 '19 at 14:26
  • Possible duplicate of [Cannot add or update a child row: a foreign key constraint fails](https://stackoverflow.com/questions/5005388/cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails) – philipxy May 29 '19 at 18:07
  • What did you learn when you checked many possible problems from answers when you googled this error message with 'site:stackoverflow.com' & with 'sql' or 'mysql'? (Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS Your FKs are doing exactly what FKs do. Find out what that is. – philipxy May 29 '19 at 18:08
  • Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DDL which includes constraints & indexes. – philipxy May 29 '19 at 18:09

1 Answers1

1

This looks like a circular reference to me.

You can't insert a company without a user, and can't insert a user without a company. You also can't insert into two tables at once.

Decide if you really need this kind of relationship. If there's a one to one relationship, you may be able to deal with just one table. If a company has more than one user, you can remove the user_id from the company table. If a user can belong to more than one company, you can create a membership table with company_id, user_id pairs as the records.

If you do, you may need to remove the foreign key and non-null constraints.

toastifer
  • 478
  • 3
  • 8