exercicio_8a6ds.sql

CREATE TABLE cliente (
  	dni VARCHAR(255),
  	nome VARCHAR(255),
  	sobrenome VARCHAR(255),
  	id BIGINT UNIQUE NOT NULL AUTO_INCREMENT,
  	PRIMARY KEY (id)
);

INSERT INTO cliente (dni,nome,sobrenome) 
VALUES 
("dni-9fa68sd7a", "Michael", "Jackson"),
("dni-f89712ty3", "Eminem", "Shade"),
("dni-da87yt132", "Snoop", "Dog");

CREATE TABLE conta (
  	agencia VARCHAR(8) NOT NULL,
  	conta VARCHAR(16) NOT NULL,
  	saldo DECIMAL(32,4),
  	id BIGINT UNIQUE NOT NULL AUTO_INCREMENT,
  	
  	PRIMARY KEY (agencia, conta)
);

-- Parte que cria relação 1 cliente TO N conta
ALTER TABLE conta 
ADD  id_cliente BIGINT NOT NULL;

ALTER TABLE conta 
ADD CONSTRAINT fk_id_cliente 
FOREIGN KEY (id_cliente) REFERENCES cliente(id);


INSERT INTO conta (agencia, conta, saldo, id_cliente)
VALUES
("0923", "a3a4a56a", 0.1, 1),
-- ("0923", "a3a4a56a", 0.1, 1), -- quebra pois 0923+a3a4a56a já existe
("5123", "b2b3b4b6", 412512.13, 2),
("5123", "c8c67c54c", 1051230.321, 2),
("5123", "d5d3d23d", 1321.3, 3),
("2958", "e1e5e32e", 12345678901234567890.2, 3);