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;
Add it in two steps, and it’s good to make it unsigned too: public function up() { Schema::create(‘priorities’, function($table) { $table->increments(‘id’, true); $table->integer(‘user_id’)->unsigned(); $table->string(‘priority_name’); $table->smallInteger(‘rank’); $table->text(‘class’); $table->timestamps(‘timecreated’); }); Schema::table(‘priorities’, function($table) { $table->foreign(‘user_id’)->references(‘id’)->on(‘users’); }); }
It occurred because you tried to create a foreign key from tblDomare.PersNR to tblBana.BanNR but/and the values in tblDomare.PersNR didn’t match with any of the values in tblBana.BanNR. You cannot create a relation which violates referential integrity.
I use SHOW CREATE TABLE mytable; This shows you the SQL statement necessary to receate mytable in its current form. You can see all the columns and their types (like DESC) but it also shows you constraint information (and table type, charset, etc.).
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
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
Mysql has a special syntax for dropping foreign key constraints: ALTER TABLE tbl_magazine_issue DROP FOREIGN KEY FK_tbl_magazine_issue_mst_users
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’;
Yes, you can enforce the constraint only when the value is not NULL. This can be easily tested with the following example: CREATE DATABASE t; USE t; CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child (id INT NULL, parent_id INT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id) ) ENGINE=INNODB; … Read more
Rails 4.x When you already have users and uploads tables and wish to add a new relationship between them. All you need to do is: just generate a migration using the following command: rails g migration AddUserToUploads user:references Which will create a migration file as: class AddUserToUploads < ActiveRecord::Migration def change add_reference :uploads, :user, index: … Read more