Ability to specify composite/compound key

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

2 Likes

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

1 Like

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?

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

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

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

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

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.

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

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 !

how to add this reference to another table