How to wirte an select with forigin keys and such

Question

I am about to setup a sql db, not decided yet for mysql, mariadb or postgresql. But before that I have set up my sql script to create the tables I need.

CREATE TABLE `teams` (
    `team_id` INT NOT NULL AUTO_INCREMENT,
    `team_name` VARCHAR(255) NOT NULL UNIQUE,
    `team_payed` ENUM('no', 'yes'),
    `team_members` JSON,
    `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`team_id`)
);

CREATE TABLE `judges` (
    `judge_id` INT NOT NULL AUTO_INCREMENT,
    `judge_name` VARCHAR(255) NOT NULL UNIQUE,
    `active` ENUM('no', 'yes'),
    `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`judge_id`)
);

CREATE TABLE `competition` (
    `comp_id` INT NOT NULL AUTO_INCREMENT,
    `comp_year` YEAR(4) NOT NULL UNIQUE,
    `comp_branches` JSON, --branch1, branch2, branch3
    `comp_name` varchar(255) NOT NULL UNIQUE,
    `timestamp` DATETIME(255) TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`comp_id`)
);

CREATE TABLE `championship` (
    `champ_id` INT NOT NULL AUTO_INCREMENT,
    `team_id` INT NOT NULL,
    `judge_id` INT NOT NULL,
    `comp_id` INT NOT NULL,
    `comp_score` INT NOT NULL,
    `comp_branch` VARCHAR(255) NOT NULL, --SELECT comp_branches FROM competition WHERE comp_id = 1,2,3
    PRIMARY KEY (`champ_id`)
);

ALTER TABLE `championship` ADD CONSTRAINT `championship_fk0` FOREIGN KEY (`team_id`) REFERENCES `teams`(`team_id`);

ALTER TABLE `championship` ADD CONSTRAINT `championship_fk1` FOREIGN KEY (`judge_id`) REFERENCES `judges`(`judge_id`);

ALTER TABLE `championship` ADD CONSTRAINT `championship_fk2` FOREIGN KEY (`comp_id`) REFERENCES `competition`(`comp_id`);

ALTER TABLE `championship` ADD CONSTRAINT `championship_fk3` FOREIGN KEY (`comp_branch`) REFERENCES `competition`(`comp_branches`);

First I need help with a sanity check, can you without me see what I am setting up here? Second, the thing I am struggeling with now is an SELECT.

I want to be able to SELECT from championship table, let me show what I want: Judge GREEN gave team YELLOW 10 points How do I get this SELECT... that is my question.

userutLl1 · Answer

To achieve what you're after you just need to join the tables together on the appropriate ID fields. This query returns the comp_score given to team YELLOW from judge GREEN:

SELECT c.comp_score
FROM championship as c
INNER JOIN teams as t ON c.team_id = t.team_id
INNER JOIN judges as j ON c.judge_id = j.judge_id
WHERE t.team_name = 'YELLOW' AND j.judge_name = 'GREEN'

The foreign key constraint simply stops you from deleting primary key records that are currently in use as a foreign key.

For example if you didn't have the foreign key constraint and you deleted the record of judge GREEN from your 'judges' table (let's assume the id for GREEN = 1), then you would no longer have a way to identify the judge for any record in the 'competition' table has a judge_id = 1.

Additionally, you can create the foreign key constraints within the create table statement:

CREATE TABLE championship (
    champ_id INT NOT NULL AUTO_INCREMENT,
    team_id INT NOT NULL,
    judge_id INT NOT NULL,
    comp_id INT NOT NULL,
    comp_score INT NOT NULL,
    comp_branch VARCHAR(255) NOT NULL,
    PRIMARY KEY (champ_id),
    CONSTRAINT championship_fk0 FOREIGN KEY (team_id) REFERENCES teams(team_id),
    CONSTRAINT championship_fk1 FOREIGN KEY (judge_id) REFERENCES judges(judge_id),
    CONSTRAINT championship_fk2 FOREIGN KEY (comp_id) REFERENCES competition(comp_id),
    CONSTRAINT championship_fk3 FOREIGN KEY (comp_branches) REFERENCES competition(comp_branches)
);

How to wirte an select with forigin keys and such

1 Answers