One column in a table referring to another column where there are duplicate data in SQL

I want to build simple database system about bike (or bicycle) rental store.

Here are the tables, and columns in parantheses:

Model (MNr, Modelname, dayprice)

Bike (MNr, CopyNr, Frame, Color)

Customer (CNr, Name, Surename, MobileNr)

Rental (CNr, MNr, CopyNr, DateOut, DateIn)

(MNr stands for model number, CNr stands for customer number).

Models are bike models, Bike table shows data on each particular bike.

There can be several bikes of each model, to distinguish between them “CopyNr” column is used, where each bike in a given model is numbered 1, 2, 3, etc.

Rental: A rental relationship always applies to only one bike and one customer.

So I put the following primary and foreign keys:

Model (MNr (PK), Modelname, dayprice)
Bike (MNr (FK), CopyNr (PK), Frame, Color)
Customer (CNr (PK), Firstame, Surname, MobileNr)
Rent (CNr (FK), MNr (FK), CopyNr (FK), DateOut, DateIn)

The “CopyNr” at “Rental” is referring to the “CopyNr” of “Bikes”. So I used “CopyNr” as primary key column.

But that column can contain duplicate values, like there can be copy nr 2 of model A and copy nr 2 of model B. And primary key columns don’t allow duplicate values.

I wrote the following in the SQL file, and when but it on PHPMyAdmin.

Here’s the code from SQL code:

[It had Norwegian names, I changed it to English, thus color names are Norwegian]

-- phpMyAdmin SQL Dump
-- version 4.8.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Feb 04, 2020 at 07:15 PM
-- Server version: 10.2.26-MariaDB-log
-- PHP Version: 7.1.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+01:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 
--

-- --------------------------------------------------------

--
-- Table structure for table `Customer`
--

CREATE TABLE `Customer` (
  `CNr` int(2) NOT NULL,
  `Firstname` varchar(20),
  `Surname` varchar(20),
  `MobileNr` varchar(8),
  PRIMARY KEY (CNr)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `Customer` (`CNr`, `Firstname`, `Surname`, `MobileNr`) VALUES
(1, 'Olav', 'Petterses', '88888888'),
(2, 'Petter', 'Olavsson', '44444444');

-- --------------------------------------------------------

--
-- Table structure for table `Model`
--

CREATE TABLE `Model` (
  `MNr` int(5) NOT NULL,
  `Modelname` varchar(20),
  `Dayprice` double DEFAULT NULL,
  PRIMARY KEY (MNr)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `Model` (`MNr`, `Modelname`, `Dayprice`) VALUES
(2, 'FirstPrice', 10),
(15, 'DBS', 50),
(16, 'DBS', 60);

-- --------------------------------------------------------

--
-- Table structure for table `Bike`
--

CREATE TABLE `Bike` (
  `MNr` int(5),
  `CopyNr` int(2) NOT NULL,
  `Frame` int(3),
  `Color` varchar(10),
   PRIMARY KEY (`CopyNr`, `MNr`),
   CONSTRAINT `Bike_ibfk_1` FOREIGN KEY (`MNr`) REFERENCES `Model` (`MNr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Bike` (`MNr`, `CopyNr`, `Frame`, `Color`) VALUES
(2, 1, 55, 'rød'),
(15, 1, 65, 'rød'),
(16, 1, 55, 'grønn'),
(2, 2, 55, 'rød'),
(15, 2, 65, 'grønn');


-- --------------------------------------------------------

--
-- Table structure for table `Rent`
--

CREATE TABLE `Rent` (
  `CNr` int(2),
  `MNr` int(5),
  `CopyNr` int(2),
  `DatoUt` date,
  `DateInn` date,
  CHECK (`DatoUt` < `DateInn`),
  CONSTRAINT `Rent_ibfk_1` FOREIGN KEY (`MNr`) REFERENCES `Model` (`MNr`),
  CONSTRAINT `Rent_ibfk_2` FOREIGN KEY (`CopyNr`) REFERENCES `Bike` (`CopyNr`),
  CONSTRAINT `Rent_ibfk_3` FOREIGN KEY (`CNr`) REFERENCES `Customer` (`CNr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Rent` (`CNr`, `MNr`, `CopyNr`, `DatoUt`, `DateInn`) VALUES
(1, 15, 1, '2020-01-01', '2020-01-30'),
(2, 15, 2, '2020-02-15', '2020-02-29');


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

When I put it on SQL on PHPAdmin and click “GO”, I got error because of CHECK ( DatoIn > DateUt) code.

And I remove that part, then I get “Rent” table empty of values.

What’s wrong am I doing?

Why is the “Rent” table empty of values when I put this on SQL on PHPMyAdmin?

Should I restructure the table relations?

And why “CHECK” doesn’t work? The error message I get where I use CHECK:

Answer

I believe you’re seeing the syntax warning due to a bug in the parser used by phpMyAdmin which improperly marks this syntax as incorrect. I think that bug specifically is already reported as https://github.com/phpmyadmin/sql-parser/issues/167.

As nick indicates, you can still use the query despite the syntax warning.

Advertisement

Answer