-1

MY QUESTION IS THE FOLLOWING :

Create a new table named frequent travelers (FreqTrav). It will only store passengers who have taken more than 2 trips with the company. It will record the passenger name, passenger number, address, phone number, total trips and total fare paid. Show your CREATE TABLE query and INSERT query. Please insert the records using subquery (i.e. insert from a SELECT query).

I Worte the following query :

        CREATE TABLE FreqTrav (

    PASSENGERNUM INT(20) 
    CHECK (PASSENGERNUM IN 
(SELECT P.PASSENGERNUM
    FROM PASSENGER P, VOYAGE V
    WHERE P.PASSENGERNUM = V.PASSENGERNUM
    GROUP BY V.PASSENGERNUM
    HAVING COUNT( * ) >2) )

    passengername CHAR (20)
    address VARCHAR(50)
    phonenumber CHAR(20)
        totaltrips INT (20) 
        CHECK (totaltrips IN 
(SELECT COUNT(PASSENGERNUM) 
FROM VOYAGE 
GROUP BY PASSENGERNUM HAVING COUNT(*) >2))
        totalfare INT (20)
     CHECK (totalfare IN
( SELECT SUM(FARE) 
FROM VOYAGE
 WHERE PASSENGERNUM IN 
(SELECT COUNT(PASSENGERNUM) 
FROM VOYAGE
 GROUP BY PASSENGERNUM HAVING COUNT(*) >2)))
        );

but It's not working for me since I tried inserting a passengernum who did't travel more than two times and it was inserted. Also , I have a question regarding the other columns is it going to be okey if the value inserted different from the values in the original table or do I have to do a check constarint same as What I have done to total trip and total fare ?

I attached my database table at the pic link below as well https://i.stack.imgur.com/ojzHt.png thanks

HODA
  • 1
  • 3

1 Answers1

1

Annoyingly, check constraints aren't implemented in MySQL, see CHECK constraint in MySQL is not working

The CHECK clause is parsed but ignored by all storage engines.

(Source: MySQL Reference Manual: Create Table)

Community
  • 1
  • 1
TastyWaffles
  • 11
  • 3