Java backend part 13

 SQL Script:

DROP DATABASE IF EXISTS FullStackDev;

CREATE DATABASE IF NOT EXISTS FullStackDev;

USE FullStackDev;

-- Create table Address --

DROP TABLE IF EXISTS `Address`;

CREATE TABLE IF NOT EXISTS `Address` (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

street VARCHAR(50) NOT NULL,

city VARCHAR(50) NOT NULL

);

-- Create table User --

DROP TABLE IF EXISTS `User`;

CREATE TABLE IF NOT EXISTS `User` (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL UNIQUE,

address_id INT UNSIGNED UNIQUE KEY,

FOREIGN KEY (address_id) REFERENCES Address(id)

);

-- Insert data

INSERT INTO `Address` (street, city) 

VALUE ('Street 1', 'city 1'),

('Street 2', 'city 2'),

('Street 3', 'city 3');

        

INSERT INTO `User` (username, address_id)

VALUE   ('user1', 1),

('user2', 2),

        ('user3', 3);

        -- ('invalid address id', 1);

SELECT * FROM `User`;