How to create relationships between SQL tables

Navigation
2

There are different ways to create a relationship in the tables, which is to make use of the Foreign Key (FK),

I have these understood ways of doing it:

1) In the case of the creation of FK by SQL code ... to create a serious FK: 

FOREINGN KEY(codigocliente)REFERENCES cliente(codigocliente),

What would you say: The KEY FORANEA is "codigocliente" making REFERENCE to the "client" table that has the codigocliente field That yes ... you would have to create the codigocliente field also in the table that inherits it with the same characteristics as in its original table. Since if this field is not created before, can not the USING be used at the time of a JOIN true? would you have to use to consult several tables the ON or the WHERE true?

2) If I create the relationships by Workbench to autogenerate code , Workbench returns something like this: 

CONSTRAINT 'cedula_c'
FOREIGN KEY ()
REFERENCES 'tallermecanico_hd'.'c_clientes' ()

This way Workbench works, I do not understand it, why does Constraint? and why do not you put anything between the parentheses of the FK?

3) In phpMyAdmin create the field as an INDEX to then open the window graphically and create the relationships from there , all very nice however I do not know what code generates from there .. sure something weird like Workbench I have to do the test ..

4) ( this is not very sure if I'm correct, this is my MAIN DOUBT ) In phpMyAdmin Do not create the field which will be the FK in the inheritance table and go to the Relationships View section and the section that says Foreign Key Restrictions will appear, Where is the name of the restriction of the FK, ( this here will create the field that we had not created before ?) Then we place if it is ON DELETE Y UPDATE CASCADE or the others .. and in the next section I get tangled because I do not know what to really put .. or is that the same if I had to create the legacy field in the table before putting it in the first field they ask me ??? finally, select the BD, the table and the field with which it will be related ...

As you can see my question is in the last way I put ... I put the others to take advantage if they can appreciate that this something wrong .. thank you for having the patience to read my question.

    
asked by HDss 08.07.2017 в 06:37
source

1 answer

1

Let the following line be a pattern for the creation of a new table: 

CREATE TABLE 'tabla' (/* fragmento */)

Next I will define only the fragments referring to the creation of a characteristic field in order to focus attention on this specific aspect.

Primary key definition 

'campoPrimario' INT NOT NULL,
PRIMARY KEY ('campoPrimario')

Definition of a composite primary key 

'campoPrimario1' INT NOT NULL,
'campoPrimario2' VARCHAR(45) NOT NULL,
PRIMARY KEY ('campoPrimario1', 'campoPrimario2')

Defining a single index 

'campoUnico' INT NOT NULL,
UNIQUE INDEX 'unico' ('campoUnico' ASC)

Defining a single compound index 

'campoUnico1' INT NOT NULL,
'campoUnico2' VARCHAR(45) NOT NULL,
UNIQUE INDEX 'unico' ('campoUnico1' ASC, 'campoUnico2' ASC)

Definition of an index 

'campoIndice' INT NOT NULL,
INDEX 'indice' ('campoUnico1' ASC)

Definition of a composite index 

'campoIndice1' INT NOT NULL,
'campoIndice2' VARCHAR(45) NOT NULL,
INDEX 'indice' ('campoIndice1' ASC, 'campoIndice2' ASC)

Foreign field definition 

'campoEnForanea' INT NOT NULL,
INDEX 'indiceForaneo' ('campoEnForanea' ASC),
CONSTRAINT 'foraneo'
  FOREIGN KEY ('campoEnForanea')
  REFERENCES 'tablaForanea' ('campoEnForanea')

Foreign key definition 

'campoEnForanea' INT NOT NULL,
INDEX 'indiceForaneo' ('campoEnForanea' ASC),
PRIMARY KEY ('campoEnForanea'),
CONSTRAINT 'foraneo'
  FOREIGN KEY ('campoEnForanea')
  REFERENCES 'tablaForanea' ('campoEnForanea')

Note that the name of the indexes previously used as «indice, unico, foraneo, indiceForaneo» must be unique in the databases. To avoid accidentally duplicating these index names you can choose any pattern, for example: 

indice         'index_tabla_campo'

unico          'tabla_campo_unico'

indiceForaneo  'fk_tablaDestino_tablaOrigen_idx'

foraneo        'fk_tablaDestino_tablaOrigen'

Of course, this last is only a recommendation from my personal point of view.

    
answered by 08.07.2017 в 08:25
mercadolibre API JQuery not defined (JQuery is not defined) [closed]