I’m trying to write the sql for a database for a sample video rental store. It has to be so that a customer can rent only 10 videos maximum at a time. Each video rental stores the date of the rental, and the date it was returned. How do I add this constraint in?
let’s assume using sql, they are:
create table member(memberID integer primary key);
create table rentals(member integer foreign key references member(memberID),
video integer foreign key references video(videoID),
dateRented datetime not null, dateReturned datetime,
constraint pk primary key (member,video));
create table video(videoID integer primary key);
now I’m thinking that to represent a video still checked out, I add an entry to the rental table with the returnedDate as null. How do I make it so only a max of 10 videos can be rented out at once per member?
TheMadProfessor says
This sort of thing is usually done via a CHECK constraint. However, these normally involve just a simple range (value < 200) or class test (NOT NULL). I’m not sure whether a table-wide test is doable purely in SQL (but probably could be as part of a stored procedure and would be a simple matter in VBA or another procedural language.) If it is possible to do in native SQL, it would be something like this:
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK ((SELECT COUNT(*) FROM table_name WHERE memberID = ::customer-id AND dateReturned IS NULL) < 10)
I believe these are implemented in SQL Server and Access via the Properties menu.
ChrisS says
this is a fairly open ended question, as how you are querying the db, your db structure and a few other questions are unanswered.
which dbms are you using? mysql?