7

Does Draw.IO support foreign key relationships? Tested it with a lot of different SQL samples (w3schools sql foreignkey) but none worked.

BrunoMartinsPro
  • 1,646
  • 1
  • 24
  • 48
  • seems it's not implemented yet.. going to implement it and will post a complete answer to all the supported SQL types i develop. – BrunoMartinsPro Mar 09 '18 at 19:25

3 Answers3

5

Due to budget issues i was only able to implement it to work with MySQL and SQL Server and SQL Server Generated Scripts. The pull request is pending on https://github.com/jgraph/drawio/pull/233.

MySQL Example:

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (OrderID) REFERENCES Persons(PersonID)
);

SQL Server Example:

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (OrderID)
    REFERENCES Persons(PersonID)
);

SQL Server Generated Script Example:

CREATE TABLE [dbo].[aspnet_Applications](
    [ApplicationName] [nvarchar](256) NOT NULL,
    [LoweredApplicationName] [nvarchar](256) NOT NULL,
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED 
(
    [ApplicationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [LoweredApplicationName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [ApplicationName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[aspnet_Users](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [LoweredUserName] [nvarchar](256) NOT NULL,
    [MobileAlias] [nvarchar](16) NULL,
    [IsAnonymous] [bit] NOT NULL,
    [LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
BrunoMartinsPro
  • 1,646
  • 1
  • 24
  • 48
1

The PR was merged, you can access this plugin via Arrange, Insert, From SQL at https://www.draw.io/?splash=0&p=sql

Using the SQL plugin

user1084282
  • 1,003
  • 7
  • 6
  • 1
    That didn't work properly for all types of SQL, specially generated SQL. I made a PR to add the features described on the answer. That GIF is no longer updated since 2016. https://brunomartins.pro/public/drawio/plugins/sql/DIO.PNG – BrunoMartinsPro May 23 '18 at 11:01
1

I rewrote the sql parser by @brunomartinspro to be more forgiving on spaces and case sensativity and support more sql database types. Also foreign key relationships now work and are drawn properly, just waiting for pr to be merged in.

https://github.com/jgraph/drawio/pull/3091

sql import fk preview

lastlink
  • 1,505
  • 2
  • 19
  • 29