Out of range column value

I am trying to link users to members in my database using foreign keys, but I get an out-of-range error when inserting a member. I don't know if I created the FK right.

ALTER TABLE members
ADD CONSTRAINT fk_user
FOREIGN KEY (UsersID) REFERENCES users(id);

When I insert a user manually it works, but when I try to insert the same user whose role is member, it doesn't work. I try to match the id according to the column but still crashed.

INSERT INTO members (NameMember, email, UsersID) VALUES ('John Doe', 'john@example.com', 1);

I have checked the data types of the id columns, verified the foreign key constraint, and attempted inserting with various user IDs. My UsersID is not AI and its type is int.

It returns this error. And my id_member is AI int and is a primary key in members data table.

Error Code: 167. Out of range value for column 'id_member' at row 1.

This is how member table was created:

CREATE TABLE `members` (
  `id_member` int(100) NOT NULL AUTO_INCREMENT,
  `NameMember` varchar(100) DEFAULT NULL,
  `LastName` varchar(100) DEFAULT NULL,
  `ClubName` varchar(100) DEFAULT NULL,
  `SponsorName` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'inactive',
  `UsersID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_member`),
  KEY `fk_user` (`UsersID`),
  CONSTRAINT `fk_user` FOREIGN KEY (`id_member`) REFERENCES `users` (`id`)

And this is how users table was created.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `gender` varchar(45) DEFAULT NULL,
  `user_id` varchar(45) DEFAULT NULL,
  `role` varchar(45) DEFAULT NULL,
  `UsersID` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

What could be the possible errors? Am I calling another ID? Is it affect if I have an id autoincrement in users' data table while in members' data table I just have one requesting id from the user and the FK UsersID?

Solution

Please consider the following query, which should work as expected. The only modification required is to change the foreign key column name from id_member to UsersID.

In the example provided below, I have intentionally used a duplicate entry for the members table with UserId = 4. Everything is functioning as anticipated.

 CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `gender` varchar(45) DEFAULT NULL,
  `user_id` varchar(45) DEFAULT NULL,
  `role` varchar(45) DEFAULT NULL,
  `UsersID` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;
CREATE TABLE `members` (
  `id_member` int(100) NOT NULL AUTO_INCREMENT,
  `NameMember` varchar(100) DEFAULT NULL,
  `LastName` varchar(100) DEFAULT NULL,
  `ClubName` varchar(100) DEFAULT NULL,
  `SponsorName` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'inactive',
  `UsersID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_member`),
  KEY `fk_user` (`UsersID`),
  CONSTRAINT `fk_user` FOREIGN KEY (`UsersID`) REFERENCES `users` (`id`));
  
  -- Inserting data into users table
INSERT INTO `users` (`Name`, `email`, `password`, `phone`, `birthday`, `gender`, `user_id`, `role`, `UsersID`) 
VALUES 
('Alice Smith', 'alice.smith@example.com', 'password123', '555-1234', '1990-05-10', 'Female', 'alice123', 'admin', 'alice001'),
('Bob Johnson', 'bob.johnson@example.com', 'password123', '555-5678', '1985-07-15', 'Male', 'bob456', 'user', 'bob002'),
('Charlie Brown', 'charlie.brown@example.com', 'password123', '555-8765', '1992-10-20', 'Male', 'charlie789', 'user', 'charlie003'),
('Dana White', 'dana.white@example.com', 'password123', '555-1122', '1988-12-05', 'Female', 'dana010', 'moderator', 'dana004');
-- Inserting data into members table
INSERT INTO `members` (`NameMember`, `LastName`, `ClubName`, `SponsorName`, `email`, `status`, `UsersID`)
VALUES
('John Doe', 'Doe', 'Example Club', 'Sponsor A', 'john.doe@example.com', 'active', 1),   -- Link to Alice Smith (id=1)
('Jane Smith', 'Smith', 'Fitness Club', 'Sponsor B', 'jane.smith@example.com', 'inactive', 2), -- Link to Bob Johnson (id=2)
('Sam Green', 'Green', 'Tech Club', 'Sponsor C', 'sam.green@example.com', 'active', 3),  -- Link to Charlie Brown (id=3)
('Duplicate', 'White', 'Adventure Club', 'Sponsor D', 'emily.white@example.com', 'inactive', 4), -- Link to Dana White (id=4)
('Emily White', 'White', 'Adventure Club', 'Sponsor D', 'emily.white@example.com', 'inactive', 4); -- Link to Dana White (id=4)


 
 select * from users;
 select * from members;

Here is the SQL Fiddle for your reference.