Good day I have a question, I'm working with 2 databases at the same time, to make a table, it works well I did some joins and it brings me the data, but, there is a data that does not bring it to me, and I was verifying and the only thing I found is:
That in my table A the data has ' varchar(4) ' and in my table C has varchar(6) the keys have the same name a.id_org = c.id_org
Do you need to be identical in the value, so that you bring me the data?
At the moment of relating 2 tables by means of PRIMARY KEY and one FOREIGN KEY you must bear in mind that:
VARCHAR, DECIMAL o INT, etc ) INT(10) o VARCHAR(15)
EXAMPLE 1
Main table
CREATE TABLE tablaUno(
id VARCHAR(10) PRIMARY KEY,
name VARCHAR(10)
);
Dependent table
CREATE TABLE tablaDos(
id VARCHAR(10) PRIMARY KEY,
tablaUno_id VARCHAR(10) NOT NULL,
CONSTRAINT fk_tablaDos_tablaUno FOREIGN KEY(tablaUno_id) REFERENCES tablaUno(id)
);
The creation of the tables is done successfully, since both id of the tablaUno as tablaUno_id of the table tablaDos are of the same type: VARCHAR and the same length 10
EXAMPLE 2
I will try to repeat the creation of the previous tables, but this time look at the length of tablaUno_id even though it is of type VARCHAR this time it has a length of 120
Main table
CREATE TABLE tablaUno(
id VARCHAR(10) PRIMARY KEY,
name VARCHAR(10)
);
Dependent table
CREATE TABLE tablaDos(
id VARCHAR(10) PRIMARY KEY,
tablaUno_id VARCHAR(120) NOT NULL,
CONSTRAINT fk_tablaDos_tablaUno FOREIGN KEY(tablaUno_id) REFERENCES tablaUno(id)
);
I WILL GIVE THE FOLLOWING ERROR
Msg 1753 Level 16 State 0 Line 1 Column 'tablaUno.id' is not the same length or scale as referencing column 'tableTable.tableUno_id' in foreign key 'fk_tableDate_tableOne'. Columns participating in a foreign key relationship must be defined with the same length and scale. Msg 1750 Level 16 State 0 Line 1 Could not create constraint or index. See previous errors.
That is very descriptive to indicate the length between the primary key and the foreign key is not the same
EXAMPLE 3
If I now try to create a table with a primary key of type VARCHAR(10) in this way
CREATE TABLE tablaUno(
id VARCHAR(10) PRIMARY KEY,
name VARCHAR(10)
);
But I try to link to a foreign key of type INT(10)
CREATE TABLE tablaDos(
id VARCHAR(10) PRIMARY KEY,
tablaUno_id INT(10) NOT NULL,
CONSTRAINT fk_tablaDos_tablaUno FOREIGN KEY(tablaUno_id) REFERENCES tablaUno(id)
);
Even though both have the same length, the type is different
I WILL GIVE THE FOLLOWING ERROR
Msg 2716 Level 16 State 1 Line 1 Column, parameter, or variable # 2: Can not specify a column width on data type int.