Search code examples
mysqlforeign-keyssequelpro

Cannot make foreign key field nullable with Sequel Pro

I'm having a defined relation to my user table as foreign key, but they don't necessarily need to be set. I.e. there must not be a related record.

In my MySQL editor (Sequel Pro) I cannot make the addressId "allow null". It ignores the input. Im using newest version.

When I create a new entry in the User table without an addressId, it says "Cannot add or update a child row: a foreign key constraint fails...". But I want to be able to add a User without an Address! Is that possible?

Or should I simply remove the foreign keys? What is the drawback of that?

CREATE TABLE `User1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `addressId` int(11) NULL DEFAULT '0',
  `created` datetime DEFAULT NULL,
  `activated` datetime DEFAULT NULL,
  `lastLogin` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`addressId`),
  KEY `FK_User_Locale` (`localeId`),
  KEY `FK_User_Address` (`addressId`),
  CONSTRAINT `FK_User_Address` FOREIGN KEY (`addressId`) REFERENCES `Address` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Solution

  • Why don't you disable foreign_key_checks by setting to foreign_key_checks=0?

    Else you may DROP the constraint if it does't suit your requirements!

    If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, they are ignored. Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships.