Why isn't the SQL code I am using working?

Question

I have homework that I got to do with my group, but my group is not going to do it so I had to do everything myself. I am already stuck on the first code. I am technically finished with the home work, but just got to apply it to My SQL Workbench. I need help with it. The task is actually in Norwegian, so I just google translated everything, and the one in the dark is the quarry we're supposed to use and it is also in Norwegian. Here is the task;

A company that operates a City cycle scheme uses the following table structure in a database in order to manage the rental of the city bikes:

Label the composite foreign key from Bicycle to Lock. The city bicycle scheme initially consists of 3,000 available bicycles, 300 bicycle racks and 6,000 electronic locks (approx. 20 per bike rack). To be able to use the By-cycle scheme, the customer must have registered with a subscription where you enter your mobile phone number, name and a payment card.

Bicycle hire is done via an app on the mobile phone when the customer is standing next to a bicycle which is placed in a lock a bicycle rack, and which releases/unlocks the bicycle. Delivered and Submitted in the table Rental is Timestamp on when the bike is taken out/put into a lock at a bike rack. Care is taken where a bicycle is placed/locked (Stand ID and Lock number in Bicycle automatically get a value when a bicycle is inserted into a lock), when a bike is rented out (taken out of a lock) gets a Stand ID and Lock number in the Bicycle NULL mark. Start date in Bicycle holder order when a bicycle was taken into use in the City cycle scheme. You currently pay until you secure the bike in a lock at a bike rack. The rental period ends by securing the bike in a lock and the customer receives a confirmation in the app that the bike is delivered. The rental amount is charged to the payment card and registered.Those who operate the By-cycle scheme want to have an overview of when a bicycle was put into use city bike, where the bikes are locked, which bikes are rented, who rents bikes, subscribers, which landlords have terminated etc...

Part 1 a) Create a script that creates the database with a given table structure, choose suitable data types yourself. The only requirement for data types is Timestamp for Released and Returned in the Rental table. After the script to create the database the script should contain the insertion of good test data to provide good results on the quarry.

A friend of mine helped me, so this is the code we used, but I keep getting error on the first code, and I believe I'll get error in the other ones to.

CREATE DATABASE bysykkel;

USE bysykkel;

CREATE TABLE Sykkel (
  SykkelID INT PRIMARY KEY,
  Startdato DATE,
  StativID INT,
  Låsnr INT,
  FOREIGN KEY (StativID, Låsnr) REFERENCES Lås (StativID, Låsnr)
);

CREATE TABLE Sykkelstativ (
  StativID INT PRIMARY KEY,
  Sted VARCHAR(255)
);

CREATE TABLE Lås (
  StativID INT,
  Låsnr INT,
  PRIMARY KEY (StativID, Låsnr)
);

CREATE TABLE Kunde (
  Mobilnr VARCHAR(8) PRIMARY KEY,
  Fornavn VARCHAR(255),
  Etternavn VARCHAR(255),
  Betalingskortnr VARCHAR(16)
);

CREATE TABLE Utleie (
  SykkelID INT,
  Utlevert TIMESTAMP,
  Mobilnr VARCHAR(8),
  Innlevert TIMESTAMP,
  Beløp DECIMAL(10, 2),
  PRIMARY KEY (SykkelID, Utlevert),
  FOREIGN KEY (SykkelID) REFERENCES Sykkel (SykkelID),
  FOREIGN KEY (Mobilnr) REFERENCES Kunde (Mobilnr)
);

INSERT INTO Sykkel (SykkelID, Startdato) VALUES
  (1, '2020-01-01'),
  (2, '2020-02-01'),
  (3, '2020-03-01'),
  (4, '2020-04-01'),
  (5, '2020-05-01');

INSERT INTO Sykkelstativ (StativID, Sted) VALUES
  (1, 'Sentrum'),
  (2, 'Grünerløkka'),
  (3, 'Majorstuen');

INSERT INTO Lås (StativID, Låsnr) VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 1),
  (2, 2),
  (2, 3),
  (3, 1),
  (3, 2),
  (3, 3);

INSERT INTO Kunde (Mobilnr, Fornavn, Etternavn, Betalingskortnr) VALUES
  ('11111111', 'Ola', 'Nordmann', '1111222233334444'),
  ('22222222', 'Kari', 'Nordmann', '2222333344445555'),
  ('33333333', 'Per', 'Hansen', '3333444455556666');

INSERT INTO Utleie (SykkelID, Utlevert, Mobilnr, Innlevert, Beløp) VALUES
  (1, '2022-03-25 10:00:00', '11111111', '2022-03-25 11:00:00', 10.00),
  (2, '2022-03-25 10:30:00', '11111111', NULL, NULL),
  (3, '2022-03-25 11:00:00', '22222222', NULL, NULL),
  (4, '2022-03-25 11:30:00', '33333333', NULL, NULL);

1

What is the error message? – FanoFN Mar 27 '23 at 01:10

FanoFN · Answer 1 · 2023-03-27T01:53:24.333

I'm guessing the error is:

Error 1824: Failed to open the referenced table 'Lås'`.

This is just a matter of ordering the create table syntax execution. Your first create table syntax for table Sykkel includes REFERENCES Lås (StativID, Låsnr) while the referenced table Lås is not created yet, how can you create other table to refer it? Which mean the solution is rather simple, just move the order of CREATE TABLE and make Lås as the first one created after the database or before any table creation that require the reference table. Therefore:

CREATE DATABASE bysykkel;

USE bysykkel;

CREATE TABLE Lås ( 
   StativID INT, 
   Låsnr INT, 
   PRIMARY KEY (StativID, Låsnr) 
);

CREATE TABLE Sykkel ( 
   SykkelID INT PRIMARY KEY, 
   Startdato DATE, 
   StativID INT, 
   Låsnr INT, 
   FOREIGN KEY (StativID, Låsnr) 
   REFERENCES Lås (StativID, Låsnr) 
);

....

Demo fiddle

Why isn't the SQL code I am using working?

1 Answers1