Foreign keys, where they belong depending on relationships

I've worked with databases a fair bit (MySQL, Oracle) though typically only DML as the systems had been previously engineered.

I'm working on a project as the sole developer, and am responsible for application development, and DBA; th开发者_开发问答e latter of course proving more problematic.

In any case - I need a refresh on foreign key constraints, and their logical placement in a model. Given the following:

dbc_user
    .user_id INT, Primary Key, Auto Increment
    // arbitrary columns

dbc_user_profile
    .user_id INT, Primary Key
    // arbitrary columns

These tables are a 1-to-1 relationship. I understand that given that relationship, their columns could be consolidated into a single table, but let's just keep them separate. The foreign key belongs then, in the dbc_user_profile table?

What about many-to-many?

dbc_user
    .user_id INT, Primary Key, Auto Increment

dbc_city
    .city_id INT, Primary Key, Auto Increment

dbc_user_city
    .city_id INT, Primary Key
    .user_id INT, Primary Key

Which tables are the referencing/referenced tables?

For some reason I can't seem to find a quick and dirty tutorial on this, that explains the relationships.

So two part question;

• Can someone suggest a tutorial for the semi-savvy that hammers out the details of DDL with regard to foreign keys? or;
• Can someone give a quick rundown on 1-to-1, 1-to-m, and m-to-m relationships and FK references?

---

A foreign key is declared on the child table and references the parent table.

In a 1-to-1 relationship, it would appear that DBC_USER is the parent and DBC_USER_PROFILE is the child. So you'd declare the foreign key on the DBC_USER_PROFILE table and reference the DBC_USER table. Something like (Oracle syntax)

CREATE TABLE dbc_user_profile (
  user_id int primary key,
  <<more columns>>
  constraint fk_user_profile_user_id foreign key (user_id)
    references dbc_user( user_id )
)

In a 1-to-m relationship, it generally pretty clear which table is the parent and which is the child. The parent has 1 row for m rows in the child. So you declare the foreign key on the child table and reference the parent table.

CREATE TABLE parent (
  parent_id int primary key,
  <<additional columns>>
);

CREATE TABLE child (
  child_id int primary key,
  parent_id int references parent( parent_id ),
  <<additional columns>>
);

For an m-to-m relationship, the foreign key would be defined on the mapping table and would reference the two parent tables. So the foreign keys would be declared on the DBC_USER_CITY table and would reference both DBC_USER and DBC_CITY. Something like

CREATE TABLE dbc_user_city (
  city_id int,
  user_id int,
  constraint pk_dbc_user_city primary key( city_id, user_id ),
  constraint fk_dbc_user_city_city_id foreign key( city_id )
    references dbc_city( city_id ),
  constraint fk_dbc_user_city_user_id foreign key( user_id )
    references dbc_user( user_id )
)

Obviously, the syntax tends to be relatively database specific. For Oracle in particular, there is a pretty complete list of the syntax options on the PSOUG site. For a more general reference that includes syntax for a variety of databases, you could use the w3schools site.