Is it possible to have a mysql table accept a null value for a primary_key column referencing a different table?

I have a table that has a column which holds the id of a row in another table. However, when table A is being populated, table B may or may not have a row ready for table A.

My question is, is it possible to have mysql prevent an invalid value from being entered but be ok with a NULL? or does a foreign key necessitate a valid related value?

So... what I'm looking for (in pseudo code) is this:

Table "person" id | name

Table "people" id | group_name | person_id (foreign key id from table person)

insert into person (1, 'joe'); 

insert into people (1, 'foo', 1)//kosher
insert into people (1, 'foo', NULL)//also kosher
insert into people(1, 'foo', 7)// should fail since there is no id 7 in the person table.

The reason I need this is that I'm having a chicken and egg issue where it makes perfect sense for the rows in the people table to be created before hand (in this example, I'm creating the groups and would like them to pre-exist the people who join them). And I realize that THIS example is silly and I would just put the group id in the person table rather than vice-versa, but in my real-world problem that is not workable.

Just curious if I need to allow any and all values in order to make this work, or if there's some wa开发者_Go百科y to allow for null.

If you set the column as nullable then it can contain NULL even if it is a foreign key referencing a column in another table.

Foreign keys can be null.

When the row in the referenced table is entered you'll have to UPDATE that row to point to it.

You set a foreign key column to accept nulls by setting the optionality of the column to NULL:

DROP TABLE IF EXISTS `example`.`tableb`;
CREATE TABLE  `example`.`tableb` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `person_id` int(10) unsigned default NULL, -- notice, !say "NOT NULL" like id
  PRIMARY KEY  (`id`),
  CONSTRAINT `FK_tableb_1` FOREIGN KEY (`person_id`) REFERENCES `tablea` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

That said, tablea has to have at least one record in it before you attempt to insert a null value into the tableb reference column. Otherwise, MySQL will throw an error (for me anyways, on 4.1).