Ability to specify composite/compound key

DBML Syntax
1

Would like ability to specify a composite/compound key for a table (and then mark it unique)

4 Likes
2

This is the one limitation from our team moving to this tool.

1 Like
3

Thanks a lot for suggesting this, we’ve added this to our roadmap and let you guys know when it’s implemented!

Quick question, are you looking at using that for export to SQL queries, for merely for diagram visualization?

1 Like
4

I am primarily modeling, however being able to have it generate would be great as well.

5

I am also interested in this, both for modeling and generating SQL queries.

1 Like
6

It would be really great to add this feature, especially when generating SQL.

7

I interest to create a composite key specifies multiple columns for a primary-key or foreign-key constraint.

8

Hi @Nspector, @926068, @forkwhilefork, @shoby, @aarayas, thank you for your requests!

Just an update that we already support composite indexes/primary keys.

Table users {
  name string
  age number
  
  Indexes {
    (name, age) [unique]
  }
}

However, composite foreign key hasn’t been supported and we’ll be looking into it soon.

2 Likes
9

Please help.

Objective:
Need to have playerID and operatorID as composite primary key for PlayerProfile table.

Result:
Not working at dbdiagram. And the doc about this is not completed:
https://www.dbml-lang.org/docs/#index-definition

DBML:

Table PlayerProfile as PPro {
    playerID bigint
    operatorID int
    Indexes {
        (playerID, operatorID) [unique]
    }
}

Expected generated MySQL code (correct and intended):

CREATE TABLE `PlayerProfile` (
    `playerID` BIGINT(20) NOT NULL,
    `operatorID` INT(11) NOT NULL,
    PRIMARY KEY (`playerID`, `operatorID`)
);

Actual generated MySQL code from dbdiagram (not working for MySQL):

CREATE TABLE `PlayerProfile` (
  `playerID` bigint,
  `operatorID` int
}    
CREATE UNIQUE INDEX `PlayerProfile_index_0` ON `PlayerProfile` (`playerID`, `operatorID`);
10

Hi @patrick,

Currently, we haven’t supported exporting composite Primary Key to MySQL in this form yet:
PRIMARY KEY (`playerID`, `operatorID`)

We will put it into our backlog! I’d imagine when it is ready, the syntax would be like:

Table PlayerProfile as PPro {
    playerID bigint [pk]
    operatorID int [pk]
}

or

Table PlayerProfile as PPro {
    playerID bigint
    operatorID int
    Indexes {
        (playerID, operatorID) [pk]
    }
}

Thank you !

11

how to add this reference to another table

12

Great to hear that you put this on your backlog. In what timeframe do you expect that you will be able to handle this? Is that weeks/months/years?

We are thinking using dbdiagram.io in our classes, and this is a major issue for us.

13

Hi @Sentla,

Glad to hear that you intend to use dbdiagram.io for your classes.

In the next several weeks, composite primary key and foreign key will be supported !

14

Hi Duy Phan,

Thanks for your reply. Great to hear that you are working on this topic! For us this removes a blocking issue to continue with dbdiagram.

Keep up the good work!

15

Hi guys, any update about composite fk?

16

Hi, I also waiting for the composite foreign key feature. Do you have any update about that?
I propose the following convention:

Ref a {
  (P.user_id1, lower(P.user_id2)) > (U.id1, U.id2)
}

another proposition is named composite index that can be used in Ref section:

Table X {
  ....
  indexes {
    my_index (P.user_id1, lower(P.user_id2))[pk]
  }
}

Ref a {
  X.my_index > (U.id1, U.id2)
}
17

I also vote for this feature, unfortunately I cannot adopt the instrument without it.

1 Like
18

We really need this feature

1 Like
19

@Duy_Phan
Has dbdiagram.io added composite primary key and foregin key feature?

20

Hi all, looks like this is already supported. Check out the “Composite foreign keys” section of the link below.

https://www.dbml.org/docs/#relationships-foreign-key-definitions