Delete on cascade en primary_key

Delete on cascade en primary_key
Posted Lun, 19/12/2011 - 11:05

Hola,

Estoy creando una nueva base de datos en postgres y tengo una duda con las foreign key.

Quiero tener una tabla personas del estilo:

CREATE TABLE personas(
id_persona SERIAL PRIMARY KEY,
nombre character varying (60),
nif character varying (10),
id_telefono integer FOREIGN KEY REFERENCES telefonos (id_telefono),
...
);

Y otra tabla telefonos:
CREATE TABLE telefonos(
id_telefono integer PRIMARY KEY,
numero integer,
tipo character (3),
...
);

La cuestión es que quiero que cuando se borra un registro de la tabla personas se borren sus telefonos de la tabla telefonos, pero como el telefono es foreign key en personas, si le añado un delete on cascade lo que hara es eliminarte a la persona cuando borres su telefono. Como puedo hacer para que sea al reves, es decir, yo QUIERO QUE AL BORRAR UNA PERSONA DE BORREN SUS TELEFONOS.

Maria

‹ Consulta Informacion de Schemas Ayuda en consulta SQL sobre el patron Entidad-Atributo-Valor ›

Opciones de visualización de comentarios

Seleccione la forma que prefiera para mostrar los comentarios y haga clic en «Guardar las opciones» para activar los cambios.

Re: Delete on cascade en primary_key

Lun, 19/12/2011 - 20:16 — doctore

Hola María.

Antes de nada me gustaría hacerte una pregunta, cuando dices:

quiero que cuando se borra un registro de la tabla personas se borren sus telefonos

¿Te refieres a que una persona puede tener más de un teléfono?. Si es así, entonces estás equivocándote a la hora de establecer la relación, ya que al emplear una única foreign key desde personas a telefonos estableces que una persona no pueda tener más uno (además del hecho de que la sintaxis que expones no es correcta).

Por ello, si puede darse el caso de que una persona pueda tener más de un teléfono, el código deberá parecerse a:

CREATE TABLE personas (
  id_persona serial PRIMARY KEY,
  nombre varchar(60)
  ...
);

CREATE TABLE telefonos (
  id_telefono serial PRIMARY KEY,
  numero integer,
  persona_id serial REFERENCES personas ON DELETE CASCADE
  ...
);

Por contra, si además de que una persona pueda tener más de un teléfono, existe la posibilidad de que un mismo teléfono sea compartido por más de una persona, tendrás que eliminar la foreign key e incluir una tercera tabla que relacione a ambas (ya que ahora pasaría a ser una many-to-many).

Un saludo.

Si, eso que dices esta muy

Mar, 20/12/2011 - 11:54 — mv.gonzalez

Si, eso que dices esta muy bien pensado, qie eñ id de persona lo ponga en la tabla telefonos, el problema es que queria utilizar la tabla telefonos para otra cosa adicional...

Me explico:
Yo queria en la tabla telefonos guardar los telefonos de todas las personas, y tambien el telefono de todas las empresas, que es otra tabla que tengo. Obviamente si pongo el id_persona en la tabla telefonos, esta ya no me sirve para la tabla empresas.

La estructura seria similar a lo siguiente:
CREATE TABLE telefonos (
id_telefono serial PRIMARY KEY,
numero integer
...
);

CREATE TABLE personas (
id_persona serial PRIMARY KEY,
nombre varchar(60),
id_telf integer REFERENCES telefonos
...
);

CREATE TABLE empresas (
id_empresa serial PRIMARY KEY,
nombre varchar(60),
tipo varchar (3),
id_telefono integer REFERENCES telefonos
...
);

Lo que pasa es que de esta manera si borro algun registro de personas o de empresas el telefono continuara estando en la tabla telefonos, y no quiero dejar mierda en la tabla telefonos sobrecargandola con datos que ya no sirven para nada, por eso queria automatizarlo con el on delete cascade, aunque creo que dada mi estructura no va a ser posible.

Re: Delete on cascade en primary_key

Mar, 20/12/2011 - 15:26 — doctore

Hola María.

Y esas empresas ¿de dónde salieron? ¿qué más sorpresas nos tienes reservadas? ;)
Bueno, vayamos paso a paso. Lo primero es aclarar la cómo se relacionan esas "por ahora" 3 tablas:

1. ¿Todas las personas tienen teléfono?
2. ¿Todas las empresas tienen teléfono?
3. ¿Un persona puede tener más de un teléfono?
4. ¿Una empresa puede tener más de un teléfono?
5. ¿Un mismo teléfono puede pertenecer a más de una persona?
6. ¿Un mismo teléfono puede pertenecer a más de una empresa?
7. ¿Un mismo teléfono puede pertenecer a una persona y a una empresa a la vez?

Sería bueno que contestases a estas preguntas para conocer qué tipo de interacción existe entre las tablas.

Un saludo.

Re: Delete on cascade en primary_key

Mié, 21/12/2011 - 07:57 — mv.gonzalez

Hola,

No hay más de estas 2 tables que necesiten interactuar con los telefonos, perdon por no haberlo explicado desde el principio.

Contesto a las preguntas:
1. ¿Todas las personas tienen teléfono? SI
2. ¿Todas las empresas tienen teléfono? SI
3. ¿Un persona puede tener más de un teléfono? SI
4. ¿Una empresa puede tener más de un teléfono? SI
5. ¿Un mismo teléfono puede pertenecer a más de una persona? NO
6. ¿Un mismo teléfono puede pertenecer a más de una empresa? NO
7. ¿Un mismo teléfono puede pertenecer a una persona y a una empresa a la vez? NO

Gracias.

Re: Delete on cascade en primary_key

Mié, 21/12/2011 - 12:03 — doctore

Hola María.

Bueno, ya parece claro que la relación que tienes es una one-to-many entre Personas-Telefonos y Empresas-Telefonos, por esa razón no puedes poner la foreign key en: personas ni empresas, ya que cuando, por ejemplo, la persona tuviesen como máximo 3 teléfonos sería necesario añadir:

CREATE TABLE personas (
  id_persona serial PRIMARY KEY,
  nombre varchar(60),
  id_telf1 integer REFERENCES telefonos
  id_telf2 integer REFERENCES telefonos
  id_telf3 integer REFERENCES telefonos
...
);

Por esa razón lo mejor es que las referencias estén en la tabla teléfono:

CREATE TABLE personas (
  id_persona serial PRIMARY KEY,
  nombre varchar(60) 
  ...
);

CREATE TABLE empresas (
  id_empresa serial PRIMARY KEY,
  nombre varchar(60) 
  ...
);

CREATE TABLE telefonos (
  id_telefono serial PRIMARY KEY,
  numero integer,
  persona_id integer REFERENCES personas ON DELETE CASCADE, 
  empresa_id integer REFERENCES empresas ON DELETE CASCADE,
  CHECK (persona_id IS NOT NULL OR empresa_id IS NOT NULL)
  CHECK (persona_id IS NULL OR empresa_id IS NULL)
  ...
);

Ahora tendrás:

1. Una persona o empresa podrá tener los teléfonos que quiera.

2. Cuando elimines a una persona o empresa, se borrarán también sus teléfonos.

3. Te aseguras que cada teléfono pertenece a una persona o a una empresa (primer CHECK).

4. Un teléfono no puede ser compartido por una persona y una empresa (segundo CHECK).

PD. Ambos CHECK se pueden fusionar sin problemas en uno solo.

Un saludo.

Re: Delete on cascade en primary_key

Mié, 21/12/2011 - 12:01 — mv.gonzalez

Muchas Gracias por la ayuda. Ha sido muy útil.