Databases
StackOverflow2013
Postsdbo
POInserting into a view with an auto increment primary key?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInserting into a view with an auto increment primary key?
    text
    copied!<p>I have a table <code>horse</code> and a view <code>view_horse</code> that selects every column from the <code>horse</code> table except the primary key (primary key is auto-increment integer) and then presents it to the user, I want to insert data into that views underlying table and naturally expect the primary key to be automatically generated. But I keep getting an SQL exception stating "field of view <code>view_horse</code> underlying doesn't have a default value" when I try to insert any data into it. </p> <p>EDIT - </p> <pre><code>CREATE TABLE IF NOT EXISTS `TRC`.`horse` ( `horse_id` INT NOT NULL AUTO_INCREMENT, `registered_name` VARCHAR(20) NOT NULL, `stable_name` VARCHAR(20) NOT NULL, `horse_birth_year` DATE NOT NULL, `horse_height` DECIMAL(3,1) NOT NULL, `horse_location` VARCHAR(50) NOT NULL DEFAULT 'TRC', `arrival_date` DATE NOT NULL, `passport_no` MEDIUMTEXT NULL, `is_deceased` TINYINT(1) NOT NULL, `arrival_weight` DECIMAL NOT NULL, `horse_sex` VARCHAR(8) NOT NULL, `microchip_no` VARCHAR(15) NULL, `date_of_death` DATE NULL, PRIMARY KEY (`horse_id`), INDEX `fk_Horses_SexLookup1_idx` (`horse_sex` ASC), CONSTRAINT `fk_Horses_SexLookup1` FOREIGN KEY (`horse_sex`) REFERENCES `TRC`.`lookup_sex` (`sex`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB USE `TRC`; CREATE OR REPLACE VIEW `TRC`.`view_horse` AS SELECT registered_name AS 'Registered Name', stable_name AS 'Stable Name', horse_birth_year AS 'Age', horse_height AS 'Height', arrival_weight AS 'Weight on Arrival', horse_sex AS 'Sex', horse_location AS 'Location', arrival_date AS 'Date of Arrival', passport_no AS 'Passport no.', microchip_no AS 'Microchip no.', is_deceased AS 'Alive?' FROM `horse`; </code></pre> <p>If I insert into the view without specifying the columns it actually completes ok. But not when I give the columns as specified in the view.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload