Relationship 0..1 in practice

Navigation
3

Having the following tables:

  
  • request
    •   
  • address
    •   
  • table
    •   

Orders can be made at the pizza shop, ie for a table. It may be a delivery (address) or it may be a travel order where it does not belong to anyone. In the SQL of the requested table, I currently have the following: 

CREATE TABLE pedido  
(  
cod_ped INT NOT NULL,  
cod_mesa INT,  
cod_end INT,  
val_ped DECIMAL(18,2),  
tipo_ped CHAR(1) NOT NULL,   
data_ins DATETIME,  
CONSTRAINT pk_cod_ped  
PRIMARY KEY(cod_ped),  
CONSTRAINT fk_cod_mesa  
FOREIGN KEY (cod_mesa)   
REFERENCES mesa(cod_mesa),  
CONSTRAINT fk_cod_end  
FOREIGN KEY (cod_end)   
REFERENCES endereco(cod_end),     
);

However, as explained, the same request can belong to both a table / address and none. By creating the foreign key, you would be forcing this request to have an address and a table. How do I solve this problem?

    
asked by anonymous 08.01.2016 / 18:39

1 answer

7

If a field is a foreign key, and at the same time can receive a null value, then it does not have to have value. However, if it has value, it must refer to the referenced table.

So, the way you created it is right. Just send null value in the fields cod_mesa and cod_end which will be a request for travel.

    
08.01.2016 / 18:48
What is the purpose of the sizeof command? Generalization of parameters in Java