Edit an improperly created constraint

Question

I think I may have wrongly created a constraint. I have three tables: Activity, Authentication, Login. I wanted Authentication to be the "primary" table, where I would insert data to create a user, and his details. It would have a one-one relation (id in Login to id in Authentication) with the newly created table, Authentication which stores session ids. The third table would have a one-many relation with multiple rows for AuthenticationID which corresponds to id of Login.

This is what I've created:

| Login | CREATE TABLE `Login` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `TimeLoggedIn` text NOT NULL,
  `sessionid` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  KEY `id_2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 |

| Authentication | CREATE TABLE `Authentication` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `userid` varchar(30) NOT NULL,
  `password` varchar(30) NOT NULL,
  `role` varchar(20) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `AuthenticationID` int(6) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `Authentication_ibfk_1` FOREIGN KEY (`id`) REFERENCES `Login` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 |

| Activity | CREATE TABLE `Activity` (
  `num` int(11) NOT NULL AUTO_INCREMENT,
  `AuthenticationID` int(6) unsigned NOT NULL,
  `TorrentMag` mediumtext NOT NULL,
  PRIMARY KEY (`num`),
  KEY `FK_myKey2` (`AuthenticationID`),
  CONSTRAINT `FK_myKey` FOREIGN KEY (`AuthenticationID`) REFERENCES `Authentication` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_myKey2` FOREIGN KEY (`AuthenticationID`) REFERENCES `Authentication` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=latin1 |

Unfortunately, when I tried to insert a new row into Authentication (which used to work till I created the constraint),

INSERT INTO Authentication (userid, password, role, email) VALUES ("user", "SeG^SU;B2_&Uhw", "user", "someone@mydomain.com");

it gave the error:

Cannot add or update a child row: a foreign key constraint fails (`episodescopy`.`Authentication`, CONSTRAINT `Authentication_ibfk_1` FOREIGN KEY (`id`) REFERENCES `Login` (`id`))

So I've inadvertently created an inverse relation of what I needed? Also I seem to have created a duplicate constraint on table Activity? How can I fix this?

Does `user` have a corresponding entry in the `Login` table? This is the first thing I would check, and is what the error seems to be hinting at. — Tim Biegeleisen, Jul 27 '17 at 06:09
user is being created. The id in Login table is be updated on creating user in Authentication table, not vice versa. — Joel G Mathew, Jul 27 '17 at 06:16
Are you sure you have this down? The user must exist in the _Login_ table _before_ you can reference it in `Authentication`. Is this the case here? — Tim Biegeleisen, Jul 27 '17 at 06:18
That's not what I want. I want user to be created in Authentication, and referenced in Login. — Joel G Mathew, Jul 27 '17 at 06:20
I tried dropping the FOREIGN KEY to create it again with `ALTER TABLE Authentication DROP FOREIGN KEY Authentication_ibfk_1;`, and then tried `ALTER TABLE Login ADD CONSTRAINT FK_Login FOREIGN KEY (id) REFERENCES Authentication (id) ON DELETE CASCADE ON UPDATE CASCADE;` but it gave the error `ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (episodescopy.#sql-3ed_1b38e, CONSTRAINT FK_Login FOREIGN KEY (id) REFERENCES Authentication (id) ON DELETE CASCADE ON UPDATE CASCADE)` — Joel G Mathew, Jul 27 '17 at 06:24
Don't understand "policing". 1. I mistook your comment to Tim; I thought it was saying you had found a solution. When I saw I seemed to have misread it, I just asked what its point is, because I don't know. The comment on your question reflects my misunderstanding when it says I guess you found DROP, ie DROP CONSTRAINT, since I thought you'd found it. — philipxy, Jul 27 '17 at 07:24
Re "policing" The downvote arrow mouseover text includes "shows no research"--and I realize you have tried some things--but your question didn't mention or show that you tried DROP so it just wastes answerers' time--like question after question. Hence [ask] & [help] links lead to info re questions about code being closable (locked out from answers) without a [mcve]. We are trying to help you. (Eg Tim & my independent suggestions regarding learning design.) — philipxy, Jul 27 '17 at 07:48
The FK name you dropped is not the one you declared. Please give all your DDL & show [output from this](https://stackoverflow.com/questions/2684472/view-all-foreign-key-constraints-for-entire-mysql-database) that I just googled via 'mysql list all foreign keys'. — philipxy, Jul 27 '17 at 07:50

score 1 · Answer 1 · answered Jul 27 '17 at 06:25

Here is a suggestion which would hopefully at least point you in the right direction. If you want to create users in the Authentication table, then any other table column which references the primary key of Authentication (namely the id) should be declared as a foreign key reference.

CREATE TABLE Login (
    id int(6) unsigned NOT NULL AUTO_INCREMENT,
    TimeLoggedIn text NOT NULL,
    sessionid varchar(255) NOT NULL,
    PRIMARY KEY (id),
    KEY id (id),
    KEY id_2 (id),
    CONSTRAINT fk_1 FOREIGN KEY (id) REFERENCES Authentication (id)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

CREATE TABLE Authentication (
    id int(6) unsigned NOT NULL AUTO_INCREMENT,
    userid varchar(30) NOT NULL,
    password varchar(30) NOT NULL,
    role varchar(20) NOT NULL,
    email varchar(50) DEFAULT NULL,
    AuthenticationID int(6) unsigned DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1

Your current setup is the opposite, requiring a user to exist in Login before it can be inserted into Authentication.

Thanks for the helpful pointers. I was having a hard time undoing the constraints. Finally, exported it to .sql, manually edited and recreated the structure. — Joel G Mathew, Jul 27 '17 at 06:41
@philipxy Who said I'm not doing it in mysql? I exported the sql to a text file, removed the invalid keys, and recreated it in mysql. — Joel G Mathew, Jul 27 '17 at 07:02
@philipxy That was before I fixed it. What's with all the policing? This is still a place where less experienced programmers can clarify doubts, isnt it? Is there some specific aspect of my question that does not follow the rules or recommendations? If so, you're welcome to offer constructive criticism or a helpful answer. — Joel G Mathew, Jul 27 '17 at 07:09

Edit an improperly created constraint

1 Answers1