Server Fault Stack Exchange
Server Fault Stack Exchange

Questions tagged [foreign-key]

10 questions
5
votes
3 answers

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 ought to be a lot more. I'm afraid that the current…
Jason Kleban
  • 786
  • 3
  • 8
  • 19
3
votes
2 answers

How to delete a record when two tables have foreign key referenced to each other?

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?
vps
  • 1,187
  • 3
  • 12
  • 12
2
votes
1 answer

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.
tcoker
  • 23
  • 4
2
votes
1 answer

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 whatever order it sees fit. This causes issues with FK…
Matthew
  • 123
  • 6
1
vote
2 answers

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)
Ian
  • 325
  • 1
  • 3
  • 19
1
vote
1 answer

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 the primary key absId is referenced? I'm using…
tintin
  • 405
  • 2
  • 5
  • 11
0
votes
1 answer

MySQL, possible to prevent two fields to be NULL or NOT NULL?

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, NULL x, y, NULL, 4 and NOT VALID…
John Smith
  • 101
  • 1
0
votes
1 answer

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` (`id`) ON DELETE SET NULL ON…
Mauro Molinari
  • 192
  • 2
  • 2
  • 10
0
votes
0 answers

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 INSERT or UPDATE a row in T, thereby writing the FK…
H.M. Müller
  • 1
  • 2
-1
votes
2 answers

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 at first seems logical, since there are…
deeped
  • 1
  • 1
  • 1