0

Hello I need to create a constraint that chekc the user is 18< before listenning to certain songs but idk how... I tried this :

ALTER TABLE Historique_ecoute
DROP CONSTRAINT IF EXISTS majorite_utilisateur
GO

ALTER TABLE Historique_ecoute
ADD CONSTRAINT majorite_utilisateur
CHECK (not (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18) 
       AND REFmusic = ( select IDmusic FROM Catalogue Where censure = 'YES') )

But I get thoses messages : Msg 1046, Level 15, State 1, Line 244 Subqueries are not allowed in this context. Only scalar expressions are allowed. Msg 4104, Level 16, State 1, Line 255 The multi-part identifier "Catalogue.annee" could not be bound.

  • Welcome to the forum. Did you get an error trying to create the constraint, or does it not work the way you would like it to do? You should add a tag that indicates what RDBMS you are using. Also create a minimal example with create table statements and some sample data as insert statements, that demonstrate the problem – Lennart May 28 at 12:51
  • Specifying what database you're using is always helpful (please add that as a tag). My guess is that you're using SQL Server but that's just a guess. In every relational database I'm aware of, constraints would need to be deterministic-- you can't have an insert that is successful one day and a failure another day-- so you'd need to use a trigger. – Justin Cave May 28 at 12:52
  • @JustinCave - Its worst than that. The data entered today can become an invalid record tomorrow without being validated by a trigger or constraint. The check needs to be done on the SELECT statement within the WHERE clause. If the RDBMS supports RLS (Row Level Security), that would be my recommended method. If not, hide the logic within a VIEW. – Michael Kutz May 28 at 13:05
  • @Lennart very well I'm still very new to this world, I edited my message with the missing informations. – Ohmy Zala May 28 at 13:57
  • @JustinCave I'm using Azure Data Studio (Microsoft SQL) it's because I must create a database project for my class that include a Check constraint and I didn't had much inspiration than this idea... – Ohmy Zala May 28 at 13:59
0

There are apparently 2 problems with you constraint:

  1. Msg 1046, Level 15, State 1, Line 244 Subqueries are not allowed in this context. Only scalar expressions are allowed.

What this means is that you can not use a subquery inside a check constraint, you can only validate such constraint row by row. Examples of valid check constraints would be:

CHECK ( x BETWEEN 1 AND 3 )
CHECK ( x > y )

I think sub-queries are allowed according to SQL-standard, but few if any vendors allow it. Apparently SQL-server is not among those that do.

so the culprit for this problem is:

AND REFmusic = ( select IDmusic FROM Catalogue Where censure = 'YES' )

Let's remove that part and try:

ALTER TABLE Historique_ecoute ADD CONSTRAINT majorite_utilisateur
   CHECK (not (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18)

My guess is that this is not allowed either, because GETDATE() is not deterministic, so a row may be valid during insert but invalid a little bit later. A constraint is never allowed to evaluate to False. During a modification of a row, the constraints can be validated, but in your case, the truth value of the constraint can change without any modification being made. What action would be taken when the constraint all the sudden evaluates to False? There is no transaction that can be rolled backed, no user that can be notified.

As a side-note, I don't understand what Utilisateurs.Date_naissance refers to?

  1. Msg 4104, Level 16, State 1, Line 255 The multi-part identifier "Catalogue.annee" could not be bound.

This error I don't understand. I can't locate annee anywhere in the constraint.

I assume that the choice of song is written to some table ( Historique_ecoute ). What you can do is create a trigger that validates the condition

CREATE TRIGGER validate_age ON Historique_ecoute   
AFTER INSERT
AS 
    IF (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18)
         RAISERROR ('You are not allowed to ...',  
         ROLLBACK TRANSACTION;  
    END;  

I don't know the exact syntax, but you should get the idea. I did copy the trigger stuff from CREATE TRIGGER

| improve this answer | |
  • Thank you for your help :) – Ohmy Zala May 28 at 19:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.