[GENERAL] Sequence

Hello.

Each user has a lot of guests. Each guest only has one user.

1. I create a table users:

CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
name varchar(256)
);

2. I create a table guests:

CREATE TABLE guests (
user_id integer,
guest_id SERIAL,
PRIMARY KEY (user_id, guest_id),
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
)

3. I add two new users:

insert into users (name) values ('alex2008');
insert into users (name) values ('jack2008');

select * from users;
user_id | name
---------+----------
1 | alex2008
2 | jack2008
(2 rows)

4. I add two new guests to the user alex2008 and one new guest to the
user jack2008:

insert into guests (user_id, name) values (1, 'Mark Twain');
insert into guests (user_id, name) values (1, 'Anna Black');
insert into guests (user_id, name) values (2, 'John Black');

select * from guests;
user_id | guest_id | name
---------+----------+------------
1 | 1 | Mark Twain
1 | 2 | Anna Black
2 | 3 | John Black
(3 rows)

I want to have a different result of insert command:

user_id | guest_id | name
---------+----------+------------
1 | 1 | Mark Twain
1 | 2 | Anna Black
2 | 1 | John Black

Sequence guests_guest_id_seq is not connected with the field user_id. It
increases in spite of values of user_id. How can I solve this problem?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general