How to create a check in a table so user can't go to the same places twice unless 6 months have-CodePudding

I need a way to make a CHECK (if the user have been to the same place in the last 6 months the input will not be allow to happen) Here is my schema:

CREATE SCHEMA code

CREATE TABLE code.place ( 
   pk_place_id VARCHAR(8), 
   place_name VARCHAR (50),
   CONSTRAINT pk_place_id PRIMARY KEY (pk_place_id)
);


CREATE TABLE code.user ( 
   pk_user_id VARCHAR(3), 
   user_name VARCHAR (50),
   CONSTRAINT pk_user_id PRIMARY KEY (pk_user_id)
);

CREATE TABLE code.visit ( 
   pk_user_id VARCHAR(3), 
   pk_place_id VARCHAR(8),
   data DATE,
   CONSTRAINT pk_user_id FOREIGN KEY (pk_user_id) REFERENCES code.user,
   CONSTRAINT pk_place_id FOREIGN KEY (pk_place_id) REFERENCES code.place
);

I have the code to show a table where only users that haven´t been to a place in the last 6 months will appear in the table. But I need to put this as a check in my visit table, so no employee will put this information on the visit table, but i am finding myself not capable of doing it so.

SELECT pk_place_id, pk_user_id, date
    FROM visita_polo
    WHERE EXTRACT(year FROM age(NOW(),data))*12   EXTRACT(month FROM age(now(),data)) > 6;

CodePudding user response：

First, add the btree_gist extension.

CREATE EXTENSION btree_gist;

Then create your table with the following exclusion.

CREATE TABLE code.visit ( 
   pk_user_id VARCHAR(3)
, pk_place_id VARCHAR(8)
,        data DATE
,  CONSTRAINT pk_user_id FOREIGN KEY (pk_user_id) REFERENCES code.user
,  CONSTRAINT pk_place_id FOREIGN KEY (pk_place_id) REFERENCES code.place
,     EXCLUDE USING gist (pk_user_id WITH =, pk_place_id WITH =, daterange(data, (data   interval '6 months')::date) WITH &&)
);

Now let's put some data in.

INSERT INTO code.user
     VALUES ('ABC', 'Albert Brian Cohen')
;

INSERT INTO code.place
     VALUES ('MADRID', 'Madrid, Spain')
;

INSERT INTO code.visit
     VALUES ('ABC', 'MADRID', CURRENT_DATE)
;

So far, so good.

INSERT INTO code.visit
     VALUES ('ABC', 'MADRID', CURRENT_DATE   interval '5 months')
;

Returns an exclusion constraint violation error. Oops! Not enough time passed.

INSERT INTO code.visit
     VALUES ('ABC', 'MADRID', CURRENT_DATE   interval '6 months')
;

Inserts as expected. All good!

The database will do this for you. No need to try and enforce this at the application layer especially since the app layer is subject to race conditions. Better to handle this at the data layer so that even application bugs cannot silently corrupt your data.