I'm about to set up a large server that assigns ports to specific objects. Every object is granted a port on creation and the ports can theoretically be assigned for years. If someone destroys their object, the port is released and is up for grabs. If I had a MySQL table containing all objects AND their ports, how would I retrieve a random free port within the range specified?

So:
Table has `id` and `port`. Query should select `number` where `between <min> and <max> and not in table`. Is anything like this possible in MySQL or would I have to get another table to jump in?

Edit
====

I currently have the following two tables:

```
CREATE TABLE `scjs_relays` (
    `id` INT(100) UNSIGNED NOT NULL AUTO_INCREMENT,
    `displayname` VARCHAR(50) NOT NULL COMMENT 'Name the relay shows up with at the overview',
    `origin` VARCHAR(15) NOT NULL COMMENT 'IP the packets originate from',
    `auth` BIT(1) NOT NULL DEFAULT b'',
    `authurl` VARCHAR(255) NULL DEFAULT '0' COMMENT 'URL to retrieve auth token from',
    `authtimeout` INT(10) UNSIGNED NULL DEFAULT '0' COMMENT 'Time before person has to be reauthed',
    `steamid` BIT(1) NOT NULL DEFAULT b'',
    `teamchat` BIT(1) NOT NULL DEFAULT b'',
    `timestamp` BIT(1) NOT NULL DEFAULT b'',
    `disconnect` BIT(1) NOT NULL DEFAULT b'',
    `playerip` BIT(1) NOT NULL DEFAULT b'',
    `node` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COMMENT='Chat relays'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `scjs_ports` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `port` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `node` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `relayid` INT(100) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COMMENT='Ports per server'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

```

However, if I try to add a foreign key I get errors. the `id` in the `scjs_users` table is AUTO\_INCREMENT because this is going to be an incremental ID, every server will have their own ID that never will be reused. The foreign key I currently have is the following:

```
ALTER TABLE `scjs_ports`
ADD CONSTRAINT `FK_relayid` FOREIGN KEY (`relayid`) REFERENCES `scjs_relays` (`id`) ON UPDATE CASCADE ON DELETE SET NULL;

```

Should that work fine? If I update the row in scjs\_relay, `id` will never change, only the other columns, so is `CASCADE` the right option here? And if a row is deleted, it's port is obviously free'd up, so it should be set to NULL.