Constraints between tables on a variable im aiming to add a constraint on the number of bicycles allowed to be stored in each terminal - the number differing between terminals.

These are my original tables

create table terminal(
terminal_id serial primary key,
terminal_town varchar(30) not null,
terminal_street varchar(30) not null,
terminal_max_capacity smallint not null,
sponsor_payment_id serial references sponsor_payment(sponsor_payment_id)
);

    create table bicycle(
bicycle_id serial primary key,
bicycle_make varchar(30) not null,
bicycle_model varchar(30) not null,
bicycle_colour varchar(30) not null,
bicycle_type varchar(14) not null,
terminal_id serial references terminal(terminal_id) on delete cascade
);


When adding this:

constraint terminal_max_capacity_check check (references bicycle(bicycle_id between 1 and terminal_max_capacity)


It didnt work.

any ideas.
Thanks
I think that I would do something like the following.

Have a (sort of) joining table (or Associative Entity in the technical jargon) called slot.

If it's a manual system and you don't actually have a physical slot for individual bicycles (i.e. bicycles are stored behind a counter and allocated on an ad-hoc basis - but I'm also assuming that you keep track of individual bikes by bicycle_id), that's OK. Just "pretend" that you have individual slots and fill them virtually in sequence until there are no more either for filling or emptying. This solution also has the advantage of being ready to go in the event of slots being unsupervised as they are in my city - just out on the street!

So, do the following:

CREATE TABLE slot
(
  slot_id SERIAL PRIMARY KEY,
  terminal_id INTEGER NOT NULL,
  bicycle_id  INTEGER NOT NULL,  -- could have 0 here for unoccupied slots if you dont' want to deal with NULLs!
  CONSTRAINT slot_terminal_bike_uq UNIQUE (terminal_id, bicycle_id), -- assumes that a given bike has a "home" terminal - YMMV
  CONSTRAINT slot_terminal_fk FOREIGN KEY (terminal_id) REFERENCES terminal (terminal_id),
  CONSTRAINT slot_bicycle_fk  FOREIGN KEY (bicycle_id)  REFERENCES bicycle  (bicycle_id)
);


Then, have the same number of slots per terminal as there are total bikes at that terminal. All you have to do to see the number of available bikes is:

SELECT COUNT(*) FROM slot WHERE bicycle_id != 0


No three-value logic to worry about.

I think that experimenting with this idea might be helpful. Obviously, I don't know your exact circumstances, but hopefully it will get you going and/or thinking! Schema design is an iterative process (see the links here). Design a first pass of your system, run some code, fix the bugs (and there will be bugs), rinse and repeat.