Login

Creating MySQL Alter table commands for Foreign Keys

Author:
vidyanand
Posted:
June 16, 2008
Language:
Python
Version:
.96
Tags:
mysql foreign-keys
Score:
0 (after 0 ratings)

When using mysql the sql that is generated by syncdb doesn't create the foreign key relationship in all cases.

This code will run through a file called create_table.sql in which you store all your create sql statements ( use "python manage.py sqlall app1 app2 > create_table.sql" ) and outputs all the neccesary alter table scripts that add the foreign key. Its not 100% proof since the generated names can end up being more than 40 characters. Need to work on that.

I have written about it a little more in detail.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/usr/bin/env python

from string import split

f = file("create_table.sql", "r")
all_lines = f.readlines()
f.close()

current_table_name = ""

for each_line in all_lines:
    
    if each_line.find( "CREATE TABLE") > -1:
        elements = split(each_line)
        current_table_name = elements[2]
    
    if each_line.find( "REFERENCES") > -1 and each_line.find("ALTER TABLE") == -1 :
        # Now we need to create the alter table commands
        # The line is in the form of  < `user_id` integer NOT NULL REFERENCES `auth_user` (`id`), >
        # It needs to get converted to
        # ALTER TABLE `crm_sector` ADD CONSTRAINT industry_id_refs_id_3656de07 
        # FOREIGN KEY (`industry_id`) REFERENCES `crm_industry` (`id`);

        elements = split(each_line)
        references_index =  elements.index("REFERENCES");
        refering_key = elements[0]
        refered_table = elements[references_index + 1]
        refered_key = elements[references_index + 2]
        if refered_key.find(",") > -1:
            refered_key = refered_key.replace(",", "")
        
        constraint_name = "fk_" + current_table_name.strip("`") + "_" +  refering_key.strip("`")
        
        alter_stmt = "ALTER TABLE " + current_table_name + \
                     " ADD CONSTRAINT " + constraint_name + \
                     " FOREIGN KEY (" + refering_key + ") REFERENCES " +\
                     refered_table + " " + refered_key;
        
        print alter_stmt;

More like this

  1. Drop all tables in MySQL database by mpasternacki 5 years, 7 months ago
  2. Custom SQL via subquery by drg006 8 years, 3 months ago
  3. Improved generic foreign key manager 2 by Nomalz 5 years, 10 months ago
  4. update primary key (and cascade to child tables) by guettli 3 years, 6 months ago
  5. Circular reference with Django ORM and Postgres without breaking NOT NULL FK constraints by pstiasny 5 months, 1 week ago

Comments

Please login first before commenting.