MySQL create table with set attribute from another table?

Question

I am a beginner at this but is it possible (in MySQL workbench) to create an attribute that is a set depended on an attribute from another table?

CREATE TABLE danes (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    nationality VARCHAR(20),
    gender CHAR(1),
    degree SET ?????????????????? from degree(level)
);

CREATE TABLE degree (
    level VARCHAR(10),
    subject VARCHAR(20),
    institutionawarding VARCHAR(20),
    yearawarded DATE,
    PRIMARY KEY (level, subject)
);

never mind I got it

I'm unsure what you're trying to achieve. Which degree's level should show up on an individual "danes" when there is no link between them? Or are you asking how to link them? — Joachim Isaksson
– Joachim Isaksson, Commented Sep 28, 2014 at 16:50
+1 to @GordonLinoff's answer, but the reason this doesn't work is that it would require new values in degree.level to alter the definition of the SET. What happens when there are more than 64 distinct values of level? (The MySQL SET data type is limited to 64 elements.) — Bill Karwin
– Bill Karwin, Commented Sep 28, 2014 at 17:14
well baiscly I want to create the attribute but I dont know how the SET type works if it can be use like linking the attibute to level of the table degrees or I acually have to create a new one i don't know — Bjartastavonin
– Bjartastavonin, Commented Sep 28, 2014 at 18:39

msrd0 · Accepted Answer · 2014-09-28 17:08:48Z

2

I am guessing that you want another table, a junction table:

CREATE TABLE DaneDegrees (
    DanesId INT REFERENCES danes(id),
    Level VARCHAR(10),
    Subject VARCHAR(20),
    FOREIGN KEY fk_level_subject(level, subject) REFERENCES Degree(level, Subject)
);

I would, however, have an INT AUTO_INCREMENT PRIMARY KEY in both Danes and Degrees.

edited Sep 28, 2014 at 17:08

msrd0

8,52910 gold badges63 silver badges95 bronze badges

answered Sep 28, 2014 at 16:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bjartastavonin · Accepted Answer · 2014-09-28 18:48:08Z

0

apparently you cant just say

<attribute_name> set(get set from another tabe)

the set thing doesn't work that way

eventhough you can apparently update it so it actually can

update table_name

attribute = (select * from another table_name)

answered Sep 28, 2014 at 18:48

Bjartastavonin

11 bronze badge

Collectives™ on Stack Overflow

MySQL create table with set attribute from another table?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related