The Tree table is the connection between the Forest table and the Species table. Think of it as two steps: 1) Starting from the Forest table, join to the Tree table (from Forest.Fo_name to Tree.Tr_forest) 2) Now that the Tree is known, join to the Species table (from Tree.species to Species.sp_name) I’d write the final … Read more
Here you go: USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘X’ AND REFERENCED_COLUMN_NAME = ‘X_id’; If you have multiple databases with similar tables/column names you may also wish to limit your query to a particular database: SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘X’ AND REFERENCED_COLUMN_NAME = ‘X_id’ AND TABLE_SCHEMA = ‘your_database_name’;
Reasons to use Foreign Keys: you won’t get Orphaned Rows you can get nice “on delete cascade” behavior, automatically cleaning up tables knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality. FKs give a … Read more
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
For a Table: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘<database>’ AND REFERENCED_TABLE_NAME = ‘<table>’; For a Column: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘<database>’ AND REFERENCED_TABLE_NAME = ‘<table>’ AND REFERENCED_COLUMN_NAME = ‘<column>’; Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.
In your table dbo.Sup_Item_Cat, it has a foreign key reference to another table. The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table. If you have SQL Server Management Studio, open it up and sp_help ‘dbo.Sup_Item_Cat‘. See which … Read more
If your cascading deletes nuke a product because it was a member of a category that was killed, then you’ve set up your foreign keys improperly. Given your example tables, you should have the following table setup: CREATE TABLE categories ( id int unsigned not null primary key, name VARCHAR(255) default null )Engine=InnoDB; CREATE TABLE … Read more
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
If you want to disable all constraints in the database just run this code: — disable all constraints EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all” To switch them back on, run: (the print is optional of course and it is just listing the tables) — enable all constraints exec sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE … Read more
Quite likely your sourcecodes_tags table contains sourcecode_id values that no longer exists in your sourcecodes table. You have to get rid of those first. Here’s a query that can find those IDs: SELECT DISTINCT sourcecode_id FROM sourcecodes_tags tags LEFT JOIN sourcecodes sc ON tags.sourcecode_id=sc.id WHERE sc.id IS NULL;