Field set_base NULL > SQL compliant

Asked

Viewed 30 times

1

Reference issue:

Delete sectors and their descendants - PHP

I have the following SQL:

CREATE TABLE IF NOT EXISTS `setores` ( 
`set_cod` int(10) NOT NULL AUTO_INCREMENT, 
`set_base` int(10) NOT NULL, 
`set_setor` varchar(50) NOT NULL, 
`set_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`set_status` enum('0','1') NOT NULL DEFAULT '0', 
PRIMARY KEY (`set_cod`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 

ALTER TABLE `setores` 
ADD CONSTRAINT `relaciona_pai` FOREIGN KEY (`set_base`) 
REFERENCES `setores` (`set_cod`) 
ON DELETE CASCADE ON UPDATE CASCADE;

However, when I add a new record, set_base comes NULL.

SQL de Insert:

`INSERT INTO setores(set_cod, set_base, set_setor, set_data, set_status) VALUES (1,1,"raiz",1,1);`

inserir a descrição da imagem aqui

How can I resolve the issue?

sql

  • INSERT INTO setores( set_base, set_setor, set_status) VALUES (1,"raiz",1); you entered so? because set_cod is auto increment and set_data is Current_timestamp so you don’t need to insert.

  • Yeah, I put it like this

1 answer

1


You must be doing something wrong in PHP, because it works right here example in sqlfiddle.

CREATE TABLE IF NOT EXISTS `setores` ( 
`set_cod` int(10) NOT NULL AUTO_INCREMENT, 
`set_base` int(10) NOT NULL, 
`set_setor` varchar(50) NOT NULL, 
`set_data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
`set_status` enum('0','1') NOT NULL DEFAULT '0', 
PRIMARY KEY (`set_cod`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 

ALTER TABLE `setores` 
ADD CONSTRAINT `relaciona_pai` FOREIGN KEY (`set_base`) 
REFERENCES `setores` (`set_cod`) 
ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO setores(set_cod, set_base, set_setor, set_status) 
            VALUES (1,1,"raiz",'0');
INSERT INTO setores(set_cod, set_base, set_setor, set_status) 
            VALUES (2,1,"filho1",'1');
INSERT INTO setores(set_cod, set_base, set_setor, set_status) 
            VALUES (3,1,"filho2",'1');
INSERT INTO setores(set_cod, set_base, set_setor, set_status) 
            VALUES (4,1,"filho3",'0');

SELECT * FROM setores WHERE set_status = '1';

The result:

inserir a descrição da imagem aqui

And also inserts right even without set_cod, take the example.

INSERT INTO setores(set_base, set_setor, set_status) VALUES (1,"raiz",'0');
INSERT INTO setores(set_base, set_setor, set_status) VALUES (1,"filho1",'1');
INSERT INTO setores(set_base, set_setor, set_status) VALUES (1,"filho2",'1');
INSERT INTO setores(set_base, set_setor, set_status) VALUES (1,"filho3",'0');

Browser other questions tagged sql

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