CODEWITHSUNDEEP
mysqlsql
Adam1867
Adam1867

Reputation: 33

Creating(and deleting entries) in another table automatically

I'm a complete beginner to SQL and I am struggling to find a solution to a pretty simple problem.

The scenario is this: When a user registers on my site they are inserted into a 'users' table with only a few columns(user_id, username, email, password, date).

I don't want to ask anymore of them when they register, however I would like each user to have an optional settings page where they can input additional user info such as forename, surname, bio etc.

To try and keep my database clean I have created a second table called 'user_info' for this info. This table includes the 'user_id' column that exists in 'users'.

What I am struggling with is linking these two tables. I want it so whenever a user is added to the 'users' table a new entry is inserted into 'user_info' with the same 'user_id'. This also needs to work for deletion of users.

So far I have gathered that the use of a FOREIGN KEY is required and have attempted to use CASCADE to solve this.

My tables look like this:

CREATE TABLE users (
user_id INT AUTO_INCREMENT NOT NULL,
username VARCHAR(20) NOT NULL default '',
email VARCHAR(100) NOT NULL default '',  
password VARCHAR(100) NOT NULL default '',
create_date DATETIME NOT NULL default '0000-00-00',
PRIMARY KEY (user_id),
UNIQUE KEY (username),
UNIQUE KEY (email)
);

CREATE TABLE user_info (
    user_id INT NOT NULL,
    forename VARCHAR(30) NOT NULL default '',
    surname VARCHAR(30) NOT NULL default '',
    dob DATE NOT NULL default '0000-00-00',
    location VARCHAR(30) NOT NULL default '',
    PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

I would appreciate any advice in this issue, along with any advice on general best practice!

Kind Regards

Adam

Upvotes: 3

Views: 118

Answers (2)

Ankit Suhail
Ankit Suhail

Reputation: 2105

I am also just a beginner at sql and the solution provided by me might not be the best. You can deal with this problem in two ways

  • Finding the last inserted id in your users table and then inserting a blank record in the user_info with the fetched id.

    Since your User ID is primary key, simply do this everytime when you insert:

SELECT max(USER_ID) FROM users

store the fetched result in a variable and then insert the value of variable in the user_info table

INSERT INTO user_info (user_id) VALUES ('.$theValueInTHeVariable.');

  • The second method is by using triggers

in this solution you can make a trigger so that whenever a new id is inserted into the users table it automatically fetches the last generated user_id and insert it into the Users_info table.

for more information on triggers refer to the following sits.

link1 link2

If you still face any problem then please post back.

Upvotes: 0

cdonner
cdonner

Reputation: 37658

Take a look at Triggers (http://dev.mysql.com/doc/refman/5.0/en/triggers.html).

Upvotes: 1

Related Questions