Insert foreign keys

Question

Insert foreign keys

Navigation

#1 by (4 votes)
#2 by (1 votes)

2

I have two tables and when I try to use a key of the 1st table in 2nd the value appears NULL and it should appear the values that appear in Aquario

CREATE TABLE Aquario(
    ANome varchar(64) PRIMARY KEY,
    Localizacao varchar(8),
    Capacidade integer(15));


CREATE TABLE Habitat(
    HNome varchar(100) PRIMARY KEY,
    Iluminamento integer(10),
    Salinidade integer(2),
    pH integer(2),
    Dureza integer(3),
    Oxigenacao integer(3),
    Temperatura integer(2),
    Percentagem_de_adequacao integer(2),
    ANome varchar(64),
    FOREIGN KEY (ANome) REFERENCES Aquario(ANome));

INSERT INTO Aquario(ANome,Localizacao,Capacidade) VALUES('Os Peixes azuis','Este',20);

INSERT INTO Habitat( HNome,Iluminamento , Salinidade,pH , Dureza, Oxigenacao, Temperatura,Percentagem_de_adequacao)  VALUES ('Os peixes',20,1,5,213,52,2,59);

mysql sql

asked by anonymous 04.11.2016 / 12:47

2 answers

1

Another wrong practice is to use the name as the primary key. usually if integer numbers are used, mysql is AUTO_INCREMENT.

Think later when the user needs to change the name if it is a primary key and referencing another table goes beyond the problem.

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

link

04.11.2016 / 13:04

How to know if three points are clockwise or not in C / C ++? How to do a Jquery that when selecting a radio button, I change the items of a select?

score 4 · Accepted Answer

The foreign key is for you to link a record from one table to another, that is, it will not automatically fetch information for you from another table. And table structure for what you wish would look like this:

CREATE TABLE Aquario(AId         integer(15) PRIMARY KEY,
                     ANome       varchar(64),
                     Localizacao varchar(8),
                     Capacidade  integer(15));


CREATE TABLE Habitat(HNome                    varchar(100) PRIMARY KEY,
                     Iluminamento             integer(10),
                     Salinidade               integer(2),
                     pH                       integer(2),
                     Dureza                   integer(3),
                     Oxigenacao               integer(3),
                     Temperatura              integer(2),
                     Percentagem_de_adequacao integer(2),
                     AId                      integer(integer),
                     FOREIGN KEY (AId) REFERENCES Aquario(AId));

INSERT INTO Aquario(AId,
                    ANome,
                    Localizacao,
                    Capacidade)
             VALUES(1,
                    'Os Peixes azuis',
                    'Este',
                    20);

INSERT INTO Habitat(HNome,
                    Iluminamento,
                    Salinidade,
                    pH,
                    Dureza,
                    Oxigenacao,
                    Temperatura,
                    Percentagem_de_adequacao,
                    AId)
             VALUES('Os peixes',
                    20,
                    1,
                    5,
                    213,
                    52,
                    2,
                    59,
                    1);

And to bring the linked data you would use the following select :

select h.hnome,
       a.nome
  from Habitat h
 inner join Aquario a on a.AId = h.AId;