Database Design: need composite key + foreign key

I have 2 tables that have a 1-M relationship:

TABLE foo
 - foo开发者_开发问答_id (PK)
 - foobar_id
 - value

TABLE bar
 - bar_id (PK)
 - foo_id (FK to foo.foo_id)
 - foobar_id
 - value

I want to make sure foo.foobar_id and foo.value are unique, and thus want to make them a composite key. If I do that, then I can probably get rid of foo.foo_id. And if I do that, how do I relate foo and bar?

I also want bar.foo_id and bar.foobar_id to be unique, but will run into problems if I delete foo.foo_id.

Suggestions on how to model this?

Just create an unique index that covers both fields. In this case only unique pairs will be allowed.

I also want bar.foo_id and bar.foobar_id to be unique, but will run into problems if I delete foo.foo_id.

You can have unique keys without them being the primary key. You can also have composite unique keys or primary keys. You can create as many indexes as you would like of any variety except primary.

You won't run into problems as far as SQL is concerned but you may with people/code that already uses those tables.

And if I do that, how do I relate foo and bar?

It looks like you already have foobar_id in bar - you can make that a foreign key of foobar_id in foo.

If that doesn't work than you may need to keep foo_id in both just for the relationship. That's a big part of what ids are for. FYI, if the id is an int it's taking up very minimal space, barely enough to worry about.