SQL QUESTION CHECK-CONSRTAINT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL QUESTION CHECK-CONSRTAINT

Author

Topic

hoda
Starting Member

3 Posts

Posted - 2015-04-01 : 17:22:46

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

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)

totalfare INT (20)

);
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.
Is there any way I can answer without using check constraint
I attached my database table at the pic link below as well http://i.stack.imgur.com/ojzHt.png thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-04-01 : 20:24:16

The SQL looks correct, however SQL Server does not support subqueries in constraints. (As far as I know, no RDBMS product does, it's only mentioned in the SQL ISO standard). The workaround would be a user-defined function that performs a query and returns a scalar value to be compared.

Since this appears to be a homework question, and we don't answer them on SQLTeam (since you won't learn if we did), I'll leave researching UDFs to you and Google. Be advised that using UDFs for such a feature will perform poorly and is considered a bad practice. (And so would using subqueries in constraints).

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 20:29:16

quote:
Originally posted by hoda

Is there any way I can answer without using check constraint

Would putting the logic in the INSERT statement do?


INSERT INTO FreqTrav(PASSENGERNUM)
SELECT PASSENGERNUM
FROM ...
GROUP BY PASSENGERNUM
HAVING COUNT(*) > 2

Kristen
Test

22859 Posts

Posted - 2015-04-01 : 20:30:30

quote:
Originally posted by robvolk

however SQL Server does not support subqueries in constraints.

Good answer though - I'd give extra marks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources