0

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;
5
  • Can you be more specific on your troubles? – Marco May 29 '17 at 9:06
  • when a user has been created in the primary table (not shown) the id is not carried over to this table. – Enju May 29 '17 at 9:19
  • What did you write to trigger this? It is not because you define a foreign key that the database will write an entry in table by itself. – Marco May 29 '17 at 9:24
  • Thanks that helped answer my question mostly. Another question is will this trigger work? This trigger is attached to the primary table. DELIMITER $$ CREATE TRIGGER 'access_fill' AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO access (id) VALUES (NEW.id); END DELIMITER ; – Enju May 29 '17 at 9:51
  • Am not a mysql expert. Try it to see if it works. Then ask a new question in case you have problems with the trigger. – Marco May 29 '17 at 10:34
2

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;
0

The answer came from marco. It requires a trigger. The trigger in this case is.

DELIMITER $$
USE `asphodel`$$
CREATE DEFINER=`admin`@`%` TRIGGER `asphodel`.`users_AFTER_INSERT` 
AFTER INSERT ON `users` 
FOR EACH ROW
BEGIN
    INSERT INTO access (id)
    VALUES (NEW.id);
END$$
DELIMITER ;

I would also like to note, I also used Lennart's suggestion of changing the PK to user_id.

3
  • You need to provide a value for access_id as well. otherwise, you won't be able to insert a second row. – ypercubeᵀᴹ May 29 '17 at 10:56
  • The value for access ID is defaulted to 0 giving the user a simple authentication level. This makes it easy too build around privileges. or at least that's what the plan is. – Enju May 29 '17 at 11:43
  • then it shouldn't be a Primary Key. – ypercubeᵀᴹ May 29 '17 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.