Skip to content

Instantly share code, notes, and snippets.

@alesvaupotic
Created January 17, 2021 09:02
Show Gist options
  • Save alesvaupotic/cc89282afd2e634e957872a0b7d862a5 to your computer and use it in GitHub Desktop.
Save alesvaupotic/cc89282afd2e634e957872a0b7d862a5 to your computer and use it in GitHub Desktop.
CREATE TABLE `uporabnik` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`ime` VARCHAR(50) NULL DEFAULT '' COLLATE 'utf8mb4_0900_ai_ci',
`org_ulica` VARCHAR(50) NULL DEFAULT '' COLLATE 'utf8mb4_0900_ai_ci',
PRIMARY KEY (`id`) USING BTREE,
INDEX `ime` (`ime`) USING BTREE
);
INSERT INTO `uporabnik` (`ime`, `org_ulica`) VALUES ('Prvi', 'Prva ulica 1');
INSERT INTO `uporabnik` (`ime`, `org_ulica`) VALUES ('Drugi', 'Druga ulica 2');
CREATE TABLE `naslov` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`uporabnik_id` INT(10) UNSIGNED NOT NULL,
`ulica` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb4_0900_ai_ci',
PRIMARY KEY (`id`) USING BTREE,
INDEX `FK_naslov_uporabnik` (`uporabnik_id`) USING BTREE,
CONSTRAINT `FK_naslov_uporabnik` FOREIGN KEY (`uporabnik_id`) REFERENCES `testing`.`uporabnik` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE VIEW `v_uporabnik` AS
select `u`.`id` AS `id`,`u`.`ime` AS `ime`,`n`.`ulica` AS `ulica`
from (`uporabnik` `u` left join `naslov` `n` on((`n`.`uporabnik_id` = `u`.`id`)));
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `naslov`;
INSERT INTO naslov (uporabnik_id, ulica) SELECT id, org_ulica FROM uporabnik;
SET FOREIGN_KEY_CHECKS=1;
SELECT * FROM v_uporabnik;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment