To find the specific error run this: SHOW ENGINE INNODB STATUS; And look in the LATEST FOREIGN KEY ERROR section. The data type for the child column must match the parent column exactly. For example, since medicalhistory.MedicalHistoryID is an INT, Patient.MedicalHistory also needs to be an INT, not a SMALLINT. Also, you should run the … Read more
Because Stage is required, all one-to-many relationships where Stage is involved will have cascading delete enabled by default. It means, if you delete a Stage entity the delete will cascade directly to Side the delete will cascade directly to Card and because Card and Side have a required one-to-many relationship with cascading delete enabled by … Read more
I’m guessing that Clients.Case_Number and/or Staff.Emp_ID are not exactly the same data type as Clients_has_Staff.Clients_Case_Number and Clients_has_Staff.Staff_Emp_ID. Perhaps the columns in the parent tables are INT UNSIGNED? They need to be exactly the same data type in both tables.
One-to-one: Use a foreign key to the referenced table: student: student_id, first_name, last_name, address_id address: address_id, address, city, zipcode, student_id # you can have a # “link back” if you need You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating … Read more
Short answer: Yes, it can be NULL or duplicate. I want to explain why a foreign key might need to be null or might need to be unique or not unique. First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That … Read more
PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships. When Pg creates an implicit index it will emit a NOTICE-level message that you can see in psql and/or the system logs, so you can see when it happens. Automatically created indexes are visible in … Read more
Here’s a simple query: SELECT t1.ID FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL The key points are: LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2. The WHERE t2.ID IS NULL clause; this … Read more
It’s true that if your primary key is just an identity value auto incremented, you would have no real use for ON UPDATE CASCADE. However, let’s say that your primary key is a 10 digit UPC bar code and because of expansion, you need to change it to a 13-digit UPC bar code. In that … Read more
I found the generated set of drop statements useful, and recommend these tweaks: Limit the generated drops to your database like this: SELECT concat(‘DROP TABLE IF EXISTS `’, table_name, ‘`;’) FROM information_schema.tables WHERE table_schema=”MyDatabaseName”; Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to … Read more
DELETE FROM TABLENAME DBCC CHECKIDENT (‘DATABASENAME.dbo.TABLENAME’, RESEED, 0) Note that this isn’t probably what you’d want if you have millions+ of records, as it’s very slow.