#!/usr/bin/perl
use warnings;
use strict;

use DBI;
use List::Util qw{ shuffle };

my $dbfile = '1.db';

my $dbh = 'DBI'->connect('dbi:SQLite:dbname=' . $dbfile, q(), q());
$dbh->do('CREATE TABLE lockers (id INT)');
$dbh->do( << '__SQL__' );
CREATE TABLE units (id VARCHAR,
                    locker INT,
                    FOREIGN KEY(locker) REFERENCES lockers(id))
__SQL__

my $insert_locker = $dbh->prepare('INSERT INTO lockers (id) VALUES (?)
+');
$insert_locker->execute($_) for 1 .. 25;

my @ids = map "u$_", 1 .. 80;
my $insert_unit = $dbh->prepare('INSERT INTO units (id, locker) VALUES
+ (?, ?)');
$insert_unit->execute($_, undef) for @ids;


my $add_locker = $dbh->prepare('UPDATE units SET locker = ? WHERE id =
+ ?');
my @lockers = shuffle(1 .. 25);
my $i = 0;
$add_locker->execute($lockers[$i++], $_) for (shuffle(@ids))[1..24];

my $unassigned = $dbh->prepare( << '__SQL__' );
SELECT id
FROM lockers AS l
WHERE NOT EXISTS (SELECT 1
                  FROM units AS u
                  WHERE u.locker = l.id)
__SQL__

$unassigned->execute;
while (my @row = $unassigned->fetchrow_array) {
    print "Free locker: @row\n";
}

unlink $dbfile;
[download]