SQL FOREIGN KEY USAGE ? what does it really do ? and when it was needed ?

Question

ok im makeing a simple database for my example, there is users data and the user company's data .

CREATE TABLE `users` (
  `UID` INT(25) NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(60) NOT NULL ,
  `password` VARCHAR(100) NOT NULL ,
  `ownername` VARCHAR(150) NOT NULL ,
  `userstatus` TINYINT(1) NOT NULL ,
  `userregistertime` DATETIME NOT NULL ,
  `userlastonline` DATETIME NOT NULL ,
  PRIMARY KEY (`UID`) ,
  INDEX `username` (`username` ASC) )
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;

CREATE TABLE `company` (
  `CID` INT(25) NOT NULL AUTO_INCREMENT ,
  `UID` INT(25) NOT NULL ,
  `companyname` VARCHAR(60) NOT NULL ,
  `companyaddress` VARCHAR(255) NOT NULL ,
  `companyemail` VARCHAR(255) NULL DEFAULT NULL ,
  `companyphone` VARCHAR(20) NOT NULL ,
  `companyimage` VARCHAR(255) NULL DEFAULT NULL ,
  `companyyahoo` VARCHAR(255) NULL DEFAULT NULL ,
  `companytwitter` VARCHAR(255) NULL DEFAULT NULL ,
  `companykaskus` VARCHAR(255) NULL DEFAULT NULL ,
  `companyfacebook` VARCHAR(255) NULL DEFAULT NULL ,
  `companytype` TINYINT(1) NOT NULL DEFAULT '0' ,
  `companystatus` TEXT NULL DEFAULT NULL ,
  `companytemplate` TEXT NULL DEFAULT NULL ,
  `companyintroduction` TEXT NULL DEFAULT NULL ,
  `partnership` TINYINT(1) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`CID`) ,
  INDEX `ownername` (`UID` ASC) ,
  INDEX `companyname` (`companyname` ASC) ,
  CONSTRAINT `ownernamecompany`
    FOREIGN KEY (`UID` )
    REFERENCES `users` (`UID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

1.why after i insert data to the users table ( uid is auto increment ) it doesnt update my company UID table ?

if you have php knowledge please see how im inserting it.

$RegisterInsert1 = $dbConnect->prepare("INSERT INTO users (
`username`, `password`, `ownername`, `userregistertime`, `userlastonline`) VALUES (
:username, :password, :ownername, :userregistertime, :userlastonline)");
$RegisterInsert1->execute($RegisterData1); 

as you see, i get, UID = 1 ( auto ) then username,pasword,ownername,etc inserted on user table. but somehow my company UID is not updated. it should be UID = 1 then the rest CID ( auto ) companyname null, etc null.

2.am i right defining a foreign key usage for?

3.please give me the best example of inserting users data + company data with the right usage from the right usage of foreign.

how im doing it right now

// INSERT USERS DB
$RegisterInsert1 = $dbConnect->prepare("INSERT INTO users (
`username`, `password`, `ownername`, `userregistertime`, `userlastonline`) VALUES (
:username, :password, :ownername, :userregistertime, :userlastonline)");
$RegisterInsert1->execute($RegisterData1);

// GET USERS GIVEN AUTO GENERATED UID
// QUESTION ? THIS one should be automated by foreign useage ?
$GetUid = $dbConnect->prepare("SELECT UID FROM users WHERE username = :username");
$GetUid->execute($RegisterData3);
$UserID = $GetUid->fetch();
$RegisterData2['UID'] = $UserID;

// INSERT COMPANY INFO + UID
$RegisterInsert2 = $dbConnect->prepare("INSERT INTO company (
`UID`,`companyphone`,`partnership`) VALUES (
:UID, :companyphone, :partnership)");
$RegisterInsert2->execute($RegisterData2);

Answer 1

The foreign key in your schema definition is the UID column in the Company table. This refers to a single row in the Users table. You can ensure that an invalid UID is never inserted into the Company table by adding a foreign key constraint...

ALTER TABLE COMPANY ADD FOREIGN KEY COMPANY_USER_FK (UID) REFERENCES USER(UID);

and this will cause attempts to add a UID into the Company name that don't exist in the User table to fail. You can add extra features so that deleting a User from the User table deletes all the matching rows in the Company table, as follows...

ALTER TABLE COMPANY ...etc... REFERENCES USER(UID) ON DELETE CASCADE;

or you can prevent users from being deleted if rows for them exist in the company table by...

ALTER TABLE COMPANY ...etc... REFERENCES USER(UID) ON DELETE RESTRICT;

Although it doesn't apply to your schema, you could also cascade changes to the UID in the Users table by...

ALTER TABLE COMPANY ...etc... REFERENCES USER(UID) ON UPDATE CASCADE;

Note that all of this only works on MySQL if the storage engine is INNODB;

Answer 2

why after i insert data to the users table ( uid is auto increment ) it doesnt update my company UID table ?

SQL does not support multiple assignment, you need to do this procedurally. You need to use (at least) one INSERT statement per table.

1. INSERT INTO users which auto-generates the UID value(s);
2. Capture auto-generated value(s) for UID;
3. INSERT INTO users supplying the captured UID value(s).

FWIW SQL Server 2008 makes this easy with an OUTPUT keyword i.e. the auto-generated values can be captured (to a staging temp table) within the INSERT statement. In MS Access, you could create a VIEW with a JOIN between the two tables then INSERT INTO the VIEW and the auto-generate value would appear automatically in both tables (only worked for a two-table VIEW). I don't know if mySQL has similar features.