0
I’m finding it very difficult to create a foreign key between two tables, whose type is varchar:
ALTER TABLE netmap ADD CONSTRAINT fk_IP FOREIGN KEY ( IP ) REFERENCES ipvalidos (IPS);
The mistake is:
Failed to execute SQL : SQL ALTER TABLE Netmap ADD CONSTRAINT fk_IP FOREIGN KEY ( IP ) REFERENCES ipvalidos (IPS) failed : Cannot add Foreign key Constraint
Before trying to make the foreign key, I gave the describe, that is like:
valid
Field Type Null Key Default Extra
IPS varchar(255) NO PRI
Netmap
Field Type Null Key Default Extra
IP varchar(15) NO MUL
MAC varchar(17) NO PRI
MASK varchar(15) NO
NETID varchar(15) NO MUL
I need to make a comparison between these tables, but I don’t know where to start.
But on a table (
netmap) the field has size 15 (varchar(15)) and in the other table (ipvalidos) The field has size 255 (varchar(255))! The problem is probably related to this. And, in the tableipvalidos, this’S' in the name of the fieldIPSby chance does not indicate plural, is not?– Pedro Gaspar
I read in the mysql documentation which string type fields participating in a Foreign key do not need to be the same size, but need to be the same Character set and collation, and both tables need to be in the same Storage engine, and tables cannot be temporary. You have the script DDL of these two tables, and reference keys?
– Pedro Gaspar
@Pedro Gaspar, I’ve already made one
ALTER TABLE ipvalidos CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; e ALTER TABLE netmap CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;Another error appeared:ALTER TABLE netmap ADD CONSTRAINTfk_IPFOREIGN KEY ( IP ) REFERENCES ipvalidos(IPS ) ; failed : Cannot add or update a child row: a foreign key constraint fails (ocsweb.#sql-640_ba7c, CONSTRAINTfk_IPFOREIGN KEY (IP) REFERENCESvalid(IPS))– Anderson Fidelis
Do you already have data in these tables? Do you have any
IPon the daughter table (netmap) not on the parent table (ipvalidos)?– Pedro Gaspar
@Pedro Gaspar, yes you have. So I still want to insist on the relationship between the two. The table valid has all the valid ips, the Netmap has the ips that are in use.
– Anderson Fidelis
And why the two fields have different sizes?/
– Pedro Gaspar
No specific reason.
– Anderson Fidelis
Then the field
netmap.IPalways stores only a single IP number, and the fieldipvalidos.IPSalso always stores only a single IP number, correct? Because the way it is, the field being bigger and having an’S' in the name, giving idea of plural, made me think from the beginning that you are using the fieldipvalidos.IPSto store a list of IP numbers, but that’s not the case then, right? Because if it is, the problem is there.– Pedro Gaspar
Perfect @Pedro Gaspar! It’s a 1:1 ratio even, in unique fields, being the
ipvalidos.IPSprimary key.– Anderson Fidelis
Even though both fields are identical, I can’t do the
Constraint– Anderson Fidelis
And are you sure that all daughter table Ips have a parent table correspondent? Because that’s what this error seems to indicate... Do the two tables have many records? Can you delete all records to do a test and try to include the key with the empty tables?
– Pedro Gaspar
Yes, in
ipvalidos, Dad, I have all the valid ips on my network, used and/or vacant, atnetmap, daughter, I only have the ips used on the network.– Anderson Fidelis
Can you delete the contents of the tables, or make a copy of the database and delete, or recreate the database as a test, to check if the error continues to occur even without records in the tables? If the error no longer occurs, the problem was in the relationship of the same parent-child records. How are you doing to make sure that all children have matching in the parent table?
– Pedro Gaspar