How to change a table to enter a foreign key field?

Asked

Viewed 1,353 times

0

Hello, I’m trying to set a field user_status in an existing name table cl_users using Mariadb in Dbeaver, the table in question will reference another already existing table with 4 status fields that have already registered values that are:

|             cl_users_status            |    
ID int NOT NULL Primary Key | varchar(15)
               0              sysmanager
               1              admin
               2              employee
               3              customer

I’m using the command

ALTER TABLE cl_users 
ADD CONSTRAINT user_status
FOREIGN KEY (id) 
REFERENCES cl_users_status(id);

When I run the above command the Dbeaver returns the following error:

SQL Error [1005] [HY000]: 
Can't create table `clinica`.`#sql-275c_16` (
errno: 150 "Foreign key constraint is incorrectly formed"
)
Can't create table `clinica`.`#sql-275c_16` (
errno: 150 "Foreign key constraint is incorrectly formed"
)
Can't create table `clinica`.`#sql-275c_16` (
errno: 150 "Foreign key constraint is incorrectly formed"
)

It’s certainly an error in the syntax, only I don’t know exactly what’s wrong, I’d like to get some help.

2 answers

0

ALTER TABLE cl_users
ADD CONSTRAINT fk_user_status
FOREIGN KEY (user_status)
REFERENCES cl_users_status(id);
  • Did not work: Error was sql
SQL Error [1072] [42000]: Key column 'user_status' doesn't exist in table
 Key column 'user_status' doesn't exist in table
 Key column 'user_status' doesn't exist in table



0


After trying with several commands I myself managed to solve the problem as follows:

ALTER TABLE clinica.cl_users
ADD user_status INT;

ALTER TABLE clinica.cl_users 
ADD CONSTRAINT fk_user_status FOREIGN KEY (user_status) not null
REFERENCES cl_users_status(id);

UPDATE clinica.cl_users
SET user_status=0
WHERE ID=0;

If anyone knows how to solve this with a single command leave it there to help anyone trying to solve the same problem.

What was missing was the field user_status table before setting it as a Foreign Key because only with the command:

ALTER TABLE clinica.cl_users 
ADD CONSTRAINT fk_user_status FOREIGN KEY (user_status) not null
REFERENCES cl_users_status(id);

The same returned an error.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.