Mysql – Table relations

foreign keyinnodbMySQL

I am pretty new to MYSQL database creation. I am familiar with much of the theory as that's all we have done in class.

What I have is a user database with an auto increment id for each user as the primary key.

I would like to make a new table that contains that id and an access level.

I am having troubles with getting this table to fill up when users are registered.

This is what I have:

CREATE TABLE access (
    id INT NOT NULL AUTO_INCREMENT,
    access_id INT (100) DEFAULT '0',
        PRIMARY KEY (access_id),
        FOREIGN KEY (id) REFERENCES users (id)
            ON UPDATE CASCADE
            ON DELETE CASCADE
)Engine=InnoDB;

Best Answer

This is too long to be a comment, so I'll make it an answer. I believe your design of the ACCESS table is wrong. I would suggest something like:

CREATE TABLE access 
( user_id INT NOT NULL
, access_id INT DEFAULT 0 NOT NULL
,    CONSTRAINT PK_ACCESS PRIMARY KEY (user_id)
,    CONSTRAINT FK_USER_ACCESS FOREIGN KEY (user_id)
                REFERENCES USERS (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE                
) Engine=InnoDB;

Things I have changed:

  • I used user_id in both table USERS and ACCESS
  • user_id is the primary key of access. I assume that several users can have the same access, and that one user cannot have more than one access.
  • user_id is not AUTO_INCREMENT in access (since it is dependent of USERS).

However, if my assumptions are correct and there's a 1-1 relationship between USERS and ACCESS you might consider adding ACCESS as an attribute of USERS. I.e.:

CREATE TABLE users 
( user_id INT NOT NULL 
, access  INT DEFAULT 0 NOT NULL
,  <other attributes>
,    CONSTRAINT PK_USER PRIMARY KEY (user_id)
) Engine=InnoDB;

This will solve your initial problem on how to populate the ACCESS table. Since your USERS table exists already, you can add the column as:

ALTER TABLE USERS
    ADD COLUMN access INT DEFAULT 0 NOT NULL;
Related Question