![]() It would be better to use correct DDL code first, and then insert correct data right from the start. Mysql> insert into teachers values (16, 2) ĬAUTION: now you need to check (and amend if necessary) all the mappings teacherid userid (in the TEACHERS table)! You are on thin ice when running the update. Mysql> insert into teachers values (16, 1000) ĮRROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`teachers`, CONSTRAINT `theforeignkey` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`)) ![]() mysql> insert into teachers values (16, NULL) mysql> update teachersĪre both the NOT NULL and the FOREIGN KEY in place now? Yes. CAVEAT: the UPDATE will (most likely) use incorrect values. If we use a valid userid instead, we can subsequently apply the foreign key constraint. MySQL allowed the erid to be 0 (which is incorrect). I DO NOT RECOMMEND THIS for real life settings as it involves using incorrect data. ![]() Now - we could do something like the next couple of steps. > foreign key (userid) references users(userid) ĮRROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-554_4`, CONSTRAINT `theforeignkey` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`)) Add Column With Default Value When adding a column to a table using ALTER, we can also specify a default value. (I think that this is the step that is giving you grief.) mysql> alter table teachers I n this tutorial, we are going to see how to add a column with a default value and how to add a default value to a column that already exists in a MySQL table. This fails, as all userids in TEACHERS are 0, and there is no user with userid 0 in USERS. Mysql> insert into teachers values (16, NULL) ĮRROR 1048 (23000): Column 'userid' cannot be null Do we really have a NOT NULL constraint now (userid)? Yes. a Postgresql server or Oracle would laugh in our face if we tried this.) alter table teachers MySQL just sets the userids to 0 (!) - as they cannot be NULL Insert into teachers values (10),(11),(12),(13),(14) Īdding a column and a NOT NULL constraint to the TEACHERS table works. seems to indicate that your situation looks a bit like this (MySQL 5.7): - both users and teachers contain data. Your comment (quote): "the teacher's table has data on it and I want to add a new column that is a foreign key to the user's table the column is not null I don't want it to be nullable when I run the query I get a foreign key constraint error".
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |