How to wirte an select with forigin keys and such-CodePudding

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.

CodePudding user response：

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)
);