MYSQL create unique id according to another column (which is a foreign key)-CodePudding

In a DB there's a table called decks which looks like this:

ID | Name
------------
1  | Deck 1
2  | Deck 2
3  | Deck 3

And then another table called words which should look like this

Deck ID | Word ID | Text
-------------------------------
1       | 1       | Cat
2       | 1       | Elephant
1       | 2       | Dog
1       | 3       | Elephant
3       | 1       | Shark

So basically this table should have a Foreign Key which points to the deck and then a key which is set to auto-increment according to the foreign key. Also, note that the ID of this table would be the combination of Deck Id and Word Id. Is this possible in MYSQL 8?

Basically what I have is this:

CREATE TABLE my_db.words (
    deck_id INT,
    text VARCHAR(100),
    FOREIGN KEY(deck_id) REFERENCES decks(id)
);

But no idea how to create this auto-increment field.

Not sure if it's important but the text column should have no effect whatsoever in the result of this generated ID.

CodePudding user response：

Keep 3 Tables. Fully Normalised.

Table 1 - deck Table 2 - word Table 3 - deck_word

Creating Table 1

CREATE TABLE my_db.deck (
    id INT NOT NULL AUTO INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY(id)
);

Creating Table 2

CREATE TABLE my_db.word (
    id INT NOT NULL AUTO INCREMENT,
    text VARCHAR(100),
    PRIMARY KEY(id)
);

Creating Table 3

CREATE TABLE my_db.deck_word (
    deck_id INT NOT NULL,
    word_id INT NOT NULL,
    FOREIGN KEY(deck_id) REFERENCES deck(id),
    FOREIGN KEY(word_id) REFERENCES word(id),
    PRIMARY KEY(deck_id, word_id)
);

According to this both the word and the deck will be connected via the deck_word table which has the composite primary key

CodePudding user response：

This is how you can create,

CREATE TABLE my_db.words (
    deck_id INT NOT NULL,
    word_id INT NOT NULL AUTO_INCREMENT,
    text VARCHAR(100),
    FOREIGN KEY(deck_id) REFERENCES decks(id),
    PRIMARY KEY(deck_id, word_id)
);

CodePudding user response：

I could suggest a different form to insert into the words table:

INSERT INTO words select 1,(select count(*) from words where deckId=1), 'Cat';
INSERT INTO words select 2,(select count(*) from words where deckId=2), 'Elephant';
INSERT INTO words select 1,(select count(*) from words where deckId=1), 'Dog';
INSERT INTO words select 1,(select count(*) from words where deckId=1), 'Elephant';
INSERT INTO words select 3,(select count(*) from words where deckId=3), 'Shark';

http://sqlfiddle.com/#!9/03c119/2

This way you will get the last number, if you are making deletion this will not work.

If the orded did not matter, you can always reorder or make a virtual field to enumerate the words.