CODEWITHSUNDEEP
pythonmysqlsqlforeign-keys
Marco
Marco

Reputation: 339

Mysql Creating a Foreign key

I need to create a foreign key for my Reviewers_POS table. I have the Reviewers_Id as my Primary key, and i want to pass it to my id_r1 from Reviewrs_POS table..

    import MySQLdb as mdb
    import sys


    conexao = mdb.connect('localhost', 'root', 'rot', 'DbOmelete')
    with conexao:  
    cur = conexao.cursor()

   cur.execute("CREATE TABLE IF NOT EXISTS Reviewers(Reviewers_Id int unsigned not null AUTO_INCREMENT, PRIMARY KEY (Reviewers_Id),Title varchar(500), Polarity INT, Review TEXT);")
   cur.execute("CREATE TABLE IF NOT EXISTS Reviewers_POS(ReviewersPOS_Id int unsigned not null PRIMARY KEY AUTO_INCREMENT, Review_POS TEXT,id_r1 integer,CONSTRAINT fk_id FOREIGN KEY (id_r1) REFERENCES Reviewers (Reviewers_Id));")

__ I'm getting this error:

Traceback (most recent call last):

  File "SQLTesteForeign.py", line 14, in <module>
    cur.execute("CREATE TABLE IF NOT EXISTS Reviewers_POS(ReviewersPOS_Id int unsigned not null PRIMARY KEY AUTO_INCREMENT, Review_POS TEXT,id_r1 integer,CONSTRAINT fk_id FOREIGN KEY (id_r1) REFERENCES Reviewers (Reviewers_Id) ON DELETE NO ACTION ON UPDATE NO ACTION);")
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, "Can't create table 'DbOmelete.Reviewers_POS' (errno: 150)")

Someone knows how to solve it? i'm thinking that i'am missing something.. because i really dont know "where" is the error..

Upvotes: 0

Views: 1760

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

Your primary key is of type int unsigned, while your foreign key is of type integer, the two are not compatible. Change your foreign key to be int unsigned and the tables will create successfully.

An SQLfiddle to test with

Upvotes: 2

Related Questions