Set_base Field NULL Conforming SQL

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.

Insert SQL:

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

How can I resolve the issue?

    
asked by anonymous 12.08.2016 / 02:53

1 answer

1

You should be doing something wrong in PHP, because here it works fine, now see the sqlfiddle example .

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:

Anditalsoinsertsrightevenwithset_cod, see 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');
    
12.08.2016 / 13:01