Java backend part 18

 SQL Script:

DROP DATABASE IF EXISTS FullStackDev;

CREATE DATABASE IF NOT EXISTS FullStackDev;

USE FullStackDev;

-- 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

);

-- 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

);

DROP TABLE IF EXISTS `UserAddress`;

CREATE TABLE `UserAddress` (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

user_id INT UNSIGNED NOT NULL,

address_id INT UNSIGNED NOT NULL,

    registered_at DATETIME DEFAULT NOW(),

    FOREIGN KEY (user_id) REFERENCES `User`(id),

    FOREIGN KEY (address_id) REFERENCES `Address`(id),

    UNIQUE KEY (user_id, address_id)

);

INSERT INTO `User` (username)

VALUE   ('user1'),

('user2'),

        ('user3');

-- Insert data

INSERT INTO `Address` (street, city) 

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

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

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

        ('Street 4', 'city 4');

        

INSERT INTO `UserAddress` (user_id, address_id) 

VALUE (1, 1),

(1, 2),

        (3, 2),

        (2, 2);