Questions tagged [foreign-key]
The foreign-key tag has no summary.
10 questions
0 votes
1 answer
2k views
How to remove orphaned foreign key constraint in MySQL?
Using MySQL 5.5. I have a table for which I can't add a foreign key: ALTER TABLE `SOURCE_TABLE` ADD CONSTRAINT `ConstraintFK` FOREIGN KEY (`otherTableID`) REFERENCES `OTHER_TABLE` ...
- 232
0 votes
0 answers
322 views
SQL Server: Are shared locks used for foreign key constraint enforcement, and how long are they kept?
Main question is in the subject. In more detail: Let's say we have a table T with a FK constraint to table U (for simplicity, to its PK, and both table use clustered indices on their PKs). Now, we ...
0 votes
1 answer
45 views
MySQL, possible to prevent two fields to be NULL or NOT NULL? [closed]
a simple table: ID, NAME, POST_ID, GROUP_ID either POST_ID or GROUP_ID must be set, but never both of them, NEITHER none of them. So, there are valid cases: ID, NAME, POST_ID, GROUP_ID x, y, 1, ...
- 101
2 votes
1 answer
992 views
Does adding a foreign key constraint in MySQL cause a table rebuild?
I need to alter a large table in order to add a foreign key. Does adding the constraint cause the table to be rebuilt? The referenced table already exists and has an index on the column.
- 23
5 votes
3 answers
176 views
Discover intended Foreign Keys from JOINS in scripts
I'm inheriting a database that has 400 tables and only 150 foreign key constraints registered. Knowing what I do about the application and looking at the table columns, it's easy to say that there ...
- 806
-1 votes
2 answers
4k views
Cannot drop index from MySQL INNODB table with foreign keys/references
I'm about to drop an index from a mySQL INNODB table. There are references defined already. When I try to alter it, it says: #1025 - Error on rename of 'X/#sql-2a5f_219828' to 'X' (errno: 150) Which ...
- 1
1 vote
1 answer
1k views
how to find foreign keys constraints?
I have a table called abs with primary key absId, this is used as a foreign key in couple of tables which I don't know. Is there a way that I can find out how find the names of those tables in which ...
- 455
2 votes
1 answer
825 views
Can I use the export/import data in SQL 2008 with FK constrained tables?
For sending data back and forth between tables having identical schema I have used the import/export task in management studio. The problem is that SQLServer seems to do the import/export ops in ...
- 123
3 votes
2 answers
27k views
How to delete a record when two tables have foreign key referenced to each other? [closed]
Delete any record of them will report an error like this: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails How to overcome this problem?
- 1,247
1 vote
2 answers
674 views
Visualizing InnoDB's Foreign Keys
What would be the best way to get a visual representation of a group of MySQL tables' foreign key constraints? (other than drawing them out by hand) Something like this: (source: postgresonline.com)
- 335