Insert foreign keys

Asked

Viewed 556 times

2

I have two tables and when I try to use a key of the 1st table in the 2nd the value appears NULL and 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 database

  • 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 in another table.

  • So how do I get the values from the 1st table to the 2nd with FOREIGN KEY ?

  • I put in the answer below

  • 1

    Thank you very much , helped a lot

2 answers

4


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 in another table. And table structure for what you want 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;

1

Another wrong practice is that you use name as the primary key. usually uses integer numbers, in mysql is AUTO_INCREMENT.

Think later when the user needs to change the name if it is primary key and make reference in another table goes from 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;

http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.