Insert into related tables

Question

Eai personal, to create an SQL for Insert in related tables, are the following tables:

tb_Customer(id_Customer INTEGER [PK], nm_Customer VARCHAR, cpf_cnpj NUMERIC)

dm_address_type(cd_address_type CHAR(1) [PK], ds_address_type VARCHAR)

tb_customer_address(id_customer [PFK],  cd_address_type CHAR(1)[PFK], street VARCHAR, lot INTEGEER, references VARCHAR, zip_code VARCHAR)

SELECT * FROM dm_address_type
cd_address_type: R , C , O
ds_address_type: Residencial, Comercial, Outros

My question is the following, to register the client 'JOAZINHO' would be the following SQL?:

Joãozinho Silva - 888,777,666-55

Residential Address: Rua das Flores, 1. CEP: 01234-567 Commercial address: Rua das Pedras, 100 Conjunto 200. CEP: 01234-567

INSERT INTO tb_Customer(nm_customer, cpf_cnpj) VALUES ("Jãozinho Silva", "888.777.666-55")

SET @ic_Customer = LAST_INSERT_ID()

INSERT INTO tb_customer_address(street, lot, references, zip_code, id_customer, cd_address_type) values ('Rua das Flores', '1', 'references', 01234-567', @id_Customer, 'R')

INSERT INTO tb_customer_address(street, lot, references, zip_code, id_customer, cd_address_type) values ('Rua das Pedras', '100 Conjunt 200', 'references', 01234-567', @id_Customer, 'C')

And another question is how many different addresses can I register for each customer? How many needed, right?

Vlww personal. Hug!

Answer 1

By your choice of TAG it has become difficult to know if you are using SQL Server or Mysql.

Therefore:

• If it is SQL Server: You cannot use LAST_INSERT_ID, you need to take the data in another way. You can do this using: SCOPE_IDENTITY(), @@IDENTITY or IDENT_CURRENT. You can read more about these 3 options at this link.

• If it is Mysql Server: I created a bank here as you said you put in yours. Still some things will depend on how you created your bank, but:

1 - When you try to run this INSERT you will receive an truncated data error because the CPF contains points. I can’t tell you if there are ways to format the CPF to insert into the table, but you can do this later, in the query and in the program that will use the data, so there is no need to record the data with the points as you did;

2 - You set the variable to @ic_Customer, but in the lines below you define @id_Customer;

3 - The word 'References' is a reserved Mysql word. It is not recommended to use reserved words in your database;

4 - In the second address, in '100 Set 200' you will have problems with truncated data. You defined that this field would be an INTEGER. Taking this example text you have a field size 15B. Integers support up to 8B (when you use the Bigint option. I would separate the house number and complement into two different fields (one for number with numerical value and another for complement with varchar);

5 - There are some errors of symbols there, the correct one would be that its code would be the following:

INSERT INTO tb_Customer(nm_customer, cpf_cnpj) VALUES ("Jãozinho Silva", "88877766655");

SET @id_Customer = LAST_INSERT_ID();

INSERT INTO tb_customer_address(street, lot, reference, zip_code, id_customer, cd_address_type) values ('Rua das Flores', '1', 'references', '01234-567', @id_Customer, 'R');

INSERT INTO tb_customer_address(street, lot, reference, zip_code, id_customer, cd_address_type) values ('Rua das Pedras', '100 Conjunt 200', 'references', 01234-567', @id_Customer, 'C')

6 - Yes, you can register multiple addresses for the same client.