MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.18


CREATE TABLE word
(
  word_id INTEGER NOT NULL AUTO_INCREMENT,
  the_word   VARCHAR (50) NOT NULL, -- I don't like having tables and columns with the same name
  pronunciation VARCHAR (50), -- allow NULLs for now - you can change it later
  CONSTRAINT word_pk PRIMARY KEY (word_id),
  CONSTRAINT word_the_word_uq UNIQUE (the_word)  -- no duplicated
);

INSERT INTO word (the_word)
VALUES
('angry'), ('enraged'), ('furious'), ('irated'), 
('love'), ('affection'), ('passion'), ('devotion');

-- is 'irated' a word? Irked perhaps?
SELECT * FROM word;


word_id
the_word
pronunciation


1
angry
null

2
enraged
null

3
furious
null

4
irated
null

5
love
null

6
affection
null

7
passion
null

8
devotion
null


CREATE TABLE synonym
(
  word_1 INTEGER NOT NULL,
  word_2 INTEGER NOT NULL,
  CONSTRAINT synonyms_not_match_ck CHECK (word_1 != word_2),
  CONSTRAINT synonyn_pk PRIMARY KEY (word_1, word_2),
  CONSTRAINT synonym_w1_fk FOREIGN KEY (word_1) REFERENCES word (word_id),
  CONSTRAINT synonym_w2_fk FOREIGN KEY (word_2) REFERENCES word (word_id)
);

INSERT INTO synonym VALUES 
(1, 2), (1, 3), (1, 4), 
(2, 1), (2, 3), (2, 4),
(3, 1), (3, 2), (3, 4),
(4, 1), (4, 2), (4, 3);

-- SELECT * FROM synonym;
CREATE TABLE antonym
(
  word_1 INTEGER NOT NULL,
  word_2 INTEGER NOT NULL,
  CONSTRAINT antonyms_not_match_ck CHECK (word_1 != word_2),
  CONSTRAINT antonyn_pk PRIMARY KEY (word_1, word_2),
  CONSTRAINT antonym_w1_fk FOREIGN KEY (word_1) REFERENCES word (word_id),
  CONSTRAINT antonym_w2_fk FOREIGN KEY (word_2) REFERENCES word (word_id)
);
INSERT INTO antonym VALUES 
(5, 6), (5, 7), (5, 8), 
(6, 5), (6, 7), (6, 8),
(7, 5), (7, 6), (7, 8),
(8, 5), (8, 6), (8, 7);
SELECT * FROM antonym
ORDER BY word_1;


word_1
word_2


5
6

5
7

5
8

6
5

6
7

6
8

7
5

7
6

7
8

8
5

8
6

8
7


-- SELECT all the synonyms of 'enraged'
SELECT w.the_word 
FROM word w 
WHERE w.word_id IN   
(
  SELECT word_2 FROM synonym 
  WHERE word_1 =
  (
    SELECT word_id FROM word WHERE the_word = 'enraged'
  )
);


the_word


angry

furious

irated


-- DELETE the synonyms of 'enraged'
DELETE FROM synonym 
WHERE word_1 = (SELECT word_id FROM word WHERE the_word = 'enraged')
OR    word_2 = (SELECT word_id FROM word WHERE the_word = 'enraged');
SELECT * FROM synonym; -- word_2 = 2 no longer exists.


word_1
word_2


3
1

4
1

1
3

4
3

1
4

3
4


-- DELETE all reference to 'furious', an example of CASCADING deletes
DELETE FROM word where the_word = 'enraged';  -- this now works because no FKs now point to it
SELECT * FROM word WHERE the_word = 'enraged';
word_id	the_word	pronunciation
1	angry	null
2	enraged	null
3	furious	null
4	irated	null
5	love	null
6	affection	null
7	passion	null
8	devotion	null