Hello, I've heard that Hypersonic SQL doesn't support foreign keys? This
is a bit confusing. I'm pretty much an SQL newbie and am having a bit of
a problem, though I suspect it's more to do with my rubbish SQL than
hypersonic.

I am trying to create two tables.

create table users (
       username varchar(255) PRIMARY KEY,
       ipaddress BIGINT)

and:

create table status (
       status VARCHAR(15),
       connectiontime INTEGER,
       username VARCHAR(255),
       FOREIGN KEY (username) REFERENCES users (username),
       PRIMARY KEY (username,connectiontime,status))

The status table doesn't seem to work. I would like it to depend on the
username (which should be unique through the whole database). I have set
the foreign key (hence the confusion) and the primary key. But the
username column is not updated from the users table. I don't understand
that. I have to update the status table separately, and then what
happens is, if I have two unique usernames in the users table and I
update the status table with the status, connectiontime and username, it
just gets duplicated.

For example, after creating the tables, inserting two users in the users
table and then updating one user in the status table I get this:

select * from users,status

Username        Ipaddress       status  connectiontime  username

name2   111222333444    Away    546564564       name2
name3           555666777888    Away    546564564       name2

I'm using v6.1 of HSQLDB.

Thanks in advance,