Home / Questions / 465374
Accepted
ortiga
Asked: 2022-06-29 12:08:55 +0800 CST 2022-06-29 12:08:55 +0800 CST

null values ​​in mssql when creating foreign key

  • 772

I create two tables in sql server as follows:

create table personas
(
    id int PRIMARY KEY not null identity,
    nombre_persona varchar(24)
)

create table coches
(
    nombre_persona int FOREIGN KEY REFERENCES personas(id) ON DELETE CASCADE,
    vehiculo varchar(24)
)

insert data:

insert into personas (nombre_persona) values ('juan')
insert into personas (nombre_persona) values ('jose')
insert into personas (nombre_persona) values ('aniceto')
insert into personas (nombre_persona) values ('raul')


insert into coches (vehiculo) values ('mercedes')
insert into coches (vehiculo) values ('seat')
insert into coches (vehiculo) values ('ferrari')
insert into coches (vehiculo) values ('porche')

but when doing the query of the car table I get the values ​​in null, I should put the person to whom each car belongs, not how do I do so that it appears?

select * from coches

nombre_persona    vehiculo
  NULL            mercedes
  NULL            seat
  NULL            ferrari
  NULL            porche
sql-server

1 Answers

  1. Best Answer

    The code you show indicates that you are not inserting values ​​into the person_name column of the car table. There is nothing that relates them so that a value automatically appears. On the other hand, you should not make the relationship between tables by name but by primary keys.

    The code should be something like this

    create table persona
(
    id int PRIMARY KEY not null identity,
    nombre_persona varchar(24)
)

create table coche
(
    id_persona int FOREIGN KEY REFERENCES persona(id) ON DELETE CASCADE,
    vehiculo VARCHAR(24)
)


insert into persona (nombre_persona) values ('juan')
insert into persona (nombre_persona) values ('jose')
insert into persona (nombre_persona) values ('aniceto')
insert into persona (nombre_persona) values ('raul')


insert into coche (id_persona, vehiculo) 
SELECT id, 'mercedes' FROM persona WHERE nombre_persona = 'juan'
UNION ALL
SELECT id, 'seat' FROM persona WHERE nombre_persona = 'jose'
UNION ALL
SELECT id, 'ferrari' FROM persona WHERE nombre_persona = 'aniceto'
UNION ALL
SELECT id, 'porche' FROM persona WHERE nombre_persona = 'raul'
UNION ALL
SELECT id, 'ford' FROM persona WHERE nombre_persona = 'raul'

SELECT *
FROM persona p
JOIN coche   c ON p.id = c.id_persona;

    If you notice, there doesn't need to be a direct one-to-one relationship between the tables. There may be people with more than one car, or there could be people without a car.

    • 0