How do I establish the JOIN with INSERT VALUES statement?
So the Foreign Key fields show due via the correct association automatically the correct data in the database instead of NULL VALUES
USE master
GO
drop database BikeRentent
go
/* create db */
CREATE DATABASE BikeRent
GO
/* use db */
USE BikeRent
GO
/* create tbl customerAccount */
CREATE TABLE customerAccount (
customerAccount_ID int IDENTITY(1,1) NOT NULL,
emailadress varchar (30) NOT NULL,
password varchar (30) NOT NULL,
constraint PK_customerAccount primary key (customerAccount_ID)
)
GO
/* create tbl customer */
CREATE TABLE klant (
customer_ID int IDENTITY(1,1) NOT NULL,
customerAccount_ID int NULL,
surname varchar (30) NOT NULL,
lastname varchar (30) NOT NULL,
birthdate date NOT NULL,
sex varchar (30) NOT NULL,
phonenr varchar (30) NOT NULL,
constraint PK_KLANT primary key (customer_ID)
)
GO
/* create tbl adress */
CREATE TABLE adress (
adress_ID int IDENTITY(1,1) NOT NULL,
customer_ID int NULL,
streetname_homenumber varchar (30) NOT NULL,
postcode varchar (30) NOT NULL,
city varchar (30) NOT NULL,
constraint PK_adress primary key (adress_ID)
)
GO
/* create tbl rent */
CREATE TABLE rent (
rent_ID int IDENTITY(1,1) NOT NULL,
adress_ID int NULL,
rentperiodestart date NOT NULL,
rentperiodeend date NOT NULL,
rentstatus int NOT NULL,
polisnr varchar (12) NOT NULL,
constraint PK_rent primary key (rent_ID)
)
GO
/* create tbl Insurance */
CREATE TABLE Insurance (
Insurance _ID int IDENTITY(1,1) NOT NULL,
rent_ID int NULL,
InsuranceCompany int NOT NULL,
constraint PK_Insurance primary key (Insurance _ID)
)
GO
/* create tbl damage */
CREATE TABLE damage (
damage_ID int IDENTITY(1,1) NOT NULL,
Insurance _ID int NULL,
damageDatum date NOT NULL,
damageComment varchar (300) NOT NULL,
constraint PK_damage primary key (damage_ID)
)
GO
/* create tbl linktable_accessories */
CREATE TABLE linktable_accessories (
linktable_accessories_ID int IDENTITY(1,1),
rent_ID int,
accessories_ID int,
constraint PK_linktable_accessories primary key (linktable_accessories_ID)
);
/* create tbl accessories */
CREATE TABLE accessories (
accessories_ID int IDENTITY(10,1) NOT NULL,
accessoriesChoise int NOT NULL,
constraint PK_accessories primary key (accessories_ID)
)
GO
/* create tbl linktable_bike */
CREATE TABLE linktable_bike (
linktable_bike_ID int IDENTITY(1,1),
rent_ID int,
bike_ID int,
constraint PK_linktable_bike primary key (linktable_bike_ID)
)
GO
/* create tbl bike */
CREATE TABLE bike
(
bike_ID int IDENTITY(1,1) NOT NULL,
bikeClass int NOT NULL,
bikeType int NOT NULL,
constraint PK_bike primary key (bike_ID)
)
GO
/* add Foreignkeys, cascade met ALTER TABLE */
alter table klant
add constraint FK_KLANT_REF_customerAccount foreign key (customerAccount_ID)
references customerAccount (customerAccount_ID)
ON UPDATE CASCADE
ON DELETE NO ACTION;
go
alter table adress
add constraint FK_adress_REF_KLANT foreign key (customer_ID)
references klant (customer_ID)
ON UPDATE CASCADE
ON DELETE NO ACTION;
go
alter table rent
add constraint FK_rent_REF_adress foreign key (adress_ID)
references adress (adress_ID)
ON UPDATE CASCADE
ON DELETE NO ACTION;
go
alter table Insurance
add constraint FK_Insurance _REF_rent foreign key (rent_ID)
references rent (rent_ID)
ON UPDATE CASCADE
ON DELETE NO ACTION;
go
alter table damage
add constraint FK_damage_REF_Insurance foreign key (Insurance _ID)
references Insurance (Insurance _ID)
ON UPDATE CASCADE
ON DELETE NO ACTION;
go
alter table linktable_accessories
add constraint FK_KOPPaccessories_REF_rent foreign key (rent_ID)
references rent (rent_ID), constraint FK_KOPPaccessories_REF_accessories foreign key (accessories_ID)
references accessories (accessories_ID)
go
alter table linktable_bike
add constraint FK_KOPPbike_REF_rent foreign key (rent_ID)
references rent (rent_ID), constraint FK_KOPPEbike_REF_bike foreign key (bike_ID)
references bike (bike_ID)
go
/* add exampledata */
INSERT INTO customerAccount (emailadress, password) VALUES ('b.Adams@hotmail.com', 'Hjkre%3k4lKm#1')
INSERT INTO customerAccount (emailadress, password) VALUES ('b.Jefferson@gmail.com', 'B3@jskKlMna32')
INSERT INTO customerAccount (emailadress, password) VALUES ('k.Tapia@yahoo.com', 'J^7@wEtYnBkLlrT')
INSERT INTO klant (surname, lastname, birthdate, sex, phonenr) VALUES ('Barack', 'Adams', '19-11-20', 'Man', '212 696-5268')
INSERT INTO klant (surname, lastname, birthdate, sex, phonenr) VALUES ('Boris', '', '1963-04-13', 'Man', '212 696-511')
INSERT INTO klant (surname, lastname, birthdate, sex, phonenr) VALUES ('Karin', 'Read', '1959-03-10', 'Vrouw', '212696-516')
INSERT INTO adress (streetname_housenumber, postcode, city) VALUES ('445 Fifth Avenue', 'Suite 32D', 'New York')
INSERT INTO adress (streetname_housenumber, postcode, city) VALUES ('452 5th Ave, 'New York', 'NY 10018')
INSERT INTO adress (streetname_housenumber, postcode, city) VALUES ('40 W 40th ', 'St, New York', 'NY 10018')
INSERT INTO rent (rentperiodestart, rentperiodeend, rentstatus, polisnr) VALUES ('2016-10-09', '2016-10-11' ,'1', 'acs127845127')
INSERT INTO rent (rentperiodestart, rentperiodeend, rentstatus, polisnr) VALUES ('2016-10-09', '2016-10-13' ,'1', 'azb144578264')
INSERT INTO rent (rentperiodestart, rentperiodeend, rentstatus, polisnr) VALUES ('2016-10-10', '2016-10-12' ,'1', 'art157113514')
INSERT INTO Insurance (InsuranceCompany) VALUES ('8')
INSERT INTO Insurance (InsuranceCompany) VALUES ('3')
INSERT INTO Insurance (InsuranceCompany) VALUES ('1')
INSERT INTO damage (damageDatum, damageComment) VALUES ('2016-10-11' , 'No return')
INSERT INTO damage (damageDatum, damageComment) VALUES ('2016-10-13' , 'The chain was broken')
INSERT INTO damage (damageDatum, damageComment) VALUES ('2016-10-12' , 'The gearswitch is broken')
INSERT INTO accessories (accessorieschooise) VALUES ('2')
INSERT INTO accessories (accessorieschooise) VALUES ('1')
INSERT INTO accessories (accessorieschooise) VALUES ('3')
INSERT INTO bike (bikeClass, bikeType) VALUES ('2', '1')
INSERT INTO bike (bikeClass, bikeType) VALUES ('1', '1')
INSERT INTO bike (bikeClass, bikeType) VALUES ('1', '2')