リレーション確認用のSQL

sample.sql

create table groups (
    id integer not null auto_increment,
    name varchar(32) not null,
    created timestamp,
    updated timestamp,
    primary key (id)
);

create table sample_users (
    id integer not null auto_increment,
    group_id integer not null,
    name varchar(32) not null,
    created timestamp,
    updated timestamp,
    primary key (id),
    foreign key (group_id) references groups (id)
);

create table mobile_phones (
    id integer not null auto_increment,
    sample_user_id integer not null,
    phone_number varchar(11) not null,
    created timestamp,
    updated timestamp,
    primary key (id),
    foreign key (sample_user_id) references sample_users (id)
);

create table job_skills (
    id integer not null auto_increment,
    name varchar(32) not null,
    created timestamp,
    updated timestamp,
    primary key (id)
);

create table user_job_skills (
    id integer not null auto_increment,
    sample_user_id integer not null,
    job_skill_id integer not null,
    created timestamp,
    updated timestamp,
    primary key (id),
    foreign key (sample_user_id) references sample_users (id),
    foreign key (job_skill_id) references job_skills (id)
);

insert into groups (name, created, updated) values ('groupA', current_timestamp, current_timestamp);
insert into groups (name, created, updated) values ('groupB', current_timestamp, current_timestamp);

insert into sample_users (group_id, name, created, updated) values 
((select id from groups where name = 'groupA'), 'user-a', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupA'), 'user-b', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupA'), 'user-c', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupB'), 'user-d', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupB'), 'user-e', current_timestamp, current_timestamp)
,((select id from groups where name = 'groupB'), 'user-f', current_timestamp, current_timestamp)
;

insert into mobile_phones (sample_user_id, phone_number, created, updated) values
((select id from sample_users where name = 'user-a'), 'number-a', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-a'), 'number-a1', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), 'number-b', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), 'number-b2', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), 'number-c', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), 'number-c2', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-d'), 'number-d', current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-d'), 'number-d2', current_timestamp, current_timestamp)
;

insert into job_skills (name, created, updated) values 
('skill-1', current_timestamp, current_timestamp)
,('skill-2', current_timestamp, current_timestamp)
,('skill-3', current_timestamp, current_timestamp)
,('skill-4', current_timestamp, current_timestamp)
,('skill-5', current_timestamp, current_timestamp)
,('skill-6', current_timestamp, current_timestamp)
;

insert into user_job_skills (sample_user_id, job_skill_id, created, updated) values 
((select id from sample_users where name = 'user-a'), (select id from job_skills where name = 'skill-1'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-a'), (select id from job_skills where name = 'skill-2'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-a'), (select id from job_skills where name = 'skill-3'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), (select id from job_skills where name = 'skill-4'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), (select id from job_skills where name = 'skill-5'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-b'), (select id from job_skills where name = 'skill-6'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), (select id from job_skills where name = 'skill-1'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), (select id from job_skills where name = 'skill-5'), current_timestamp, current_timestamp)
,((select id from sample_users where name = 'user-c'), (select id from job_skills where name = 'skill-6'), current_timestamp, current_timestamp)
;