Mysql database - primary key problem

I have 2 tables: let say table 1 (userid, username, password) and table 2 (userid, a, b, c).

table 1 has a userid field (primary key), table 2 also has userid (references table 1).

If I do an insert statement, to add a row to table 2, how does table 2 generate the userid field?

Edit: I want to inser开发者_高级运维t things into table 2 where the userid in table 2 MUST match userid in table 1 - what constraints do I need to set up

The database doesn't understand the relationship between table1 and table2 unless you explicitly specify it. You can use SQL foreign keys if you are using a database engine the supports them (InnoDB).

CREATE TABLE table1 (
    userid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL);
CREATE TABLE table2 (
    rowid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userid INTEGER NOT NULL
        REFERENCES table1(userid)
        ON DELETE CASCADE ON UPDATE CASCADE
    -- other columns here
);

The REFERENCES option will tell the database engine that table2.userid requires that a row exist in table1 such that table1.userid = table2.userid. The ON DELETE and ON UPDATE clauses will cause the deletion of a row in table1 to automatically delete/update rows in table2.

You cannot create a row in table2 until you have created a row for the user in table1. The row in table1 will auto-generate the userid. When you insert into table2, you will explicitly include the user id from table1 to establish the linkage between the rows.

If the userid field on table2 is not autoincrement then you have to provide the value or the insert will fail.

If you have defined userID in table2 as foriegn key constraint, then you will need to have the corresponding userID in the table1 as reference when inserting rows to table2, else if you have not defined any such constraints, then it will not matter.

Keep in mind, if the constraint is defined, then table2 will not generate ID by itself. It would require the ID to be present in the table1 column which is referenced.

If username and password is not null then don't insert in tabel 1 and give error