SQL Server ER Diagram Guide

SQL Server schemas usually reach a diagram through a script: a file from SSMS's Generate Scripts task, an Entity Framework migration, or a hand-written deployment script. The SQL is ordinary T-SQL, but it carries habits most diagram tools were not written around. Bracket-quoted identifiers, identity columns, schema prefixes, and foreign keys added in separate ALTER TABLE statements all appear in the same file.

Brackets, identity columns, and defaults

Identifiers can be wrapped in square brackets, and generated scripts usually are: [sales].[Customers], [CustomerId]. Brackets are required for reserved words and names with spaces; a closing bracket inside a name is doubled. dbo.Orders and [dbo].[Orders] are the same name.

A representative pair of tables from a scripted database:

CREATE TABLE [sales].[Customers] (
    [CustomerId]  INT           IDENTITY(1,1) NOT NULL,
    [Email]       NVARCHAR(320) NOT NULL,
    [DisplayName] NVARCHAR(120) NULL,
    [CreatedAt]   DATETIME2(3)  NOT NULL
        CONSTRAINT [DF_Customers_CreatedAt] DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([CustomerId])
);

CREATE TABLE [sales].[Orders] (
    [OrderId]    UNIQUEIDENTIFIER NOT NULL
        CONSTRAINT [DF_Orders_OrderId] DEFAULT (NEWID()),
    [CustomerId] INT              NOT NULL,
    [Status]     VARCHAR(20)      NOT NULL,
    [PlacedAt]   DATETIMEOFFSET(7) NOT NULL,
    [Total]      DECIMAL(12,2)     NOT NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED ([OrderId])
);

IDENTITY(1,1) means seed 1, increment 1: the value is generated on insert and cannot be written without SET IDENTITY_INSERT. In a diagram it is an auto-increment badge. The named DEFAULT constraints supply values when a column is omitted, and NEWID() is a random GUID per row. PRIMARY KEY NONCLUSTERED says the key is not the table's physical order.

Types that carry meaning

• NVARCHAR(n) stores Unicode; n counts UTF-16 code units up to 4000, with NVARCHAR(MAX) above that. VARCHAR(n) uses a code page, so conversion can lose characters.
• DATETIME2(p) has 0 to 7 fractional-second digits and a wider range than legacy DATETIME, which is accurate to roughly 3.33 ms. DATETIMEOFFSET(p) also stores the UTC offset.
• UNIQUEIDENTIFIER is a 16-byte GUID. NEWID() produces a random value; NEWSEQUENTIALID() produces an ordered one, which matters when the GUID is a clustered key.
• DECIMAL(p,s) is the type for money. FLOAT and REAL are approximate, and MONEY has a fixed four-decimal scale and a history of rounding surprises.

Schemas: two-part names

Every object belongs to a schema, and dbo is the default. A sales application can keep its tables in sales and its reference data in catalog, which is why generated scripts write [sales].[Orders] rather than Orders. An unqualified name resolves through the user's default schema, so the same script can create objects in different schemas depending on who runs it.

Schemas matter because two tables named Orders can coexist in different schemas. If a tool strips the prefix, they collapse into one box and edges attach to the wrong table. Group entities by schema when there is more than one, or keep the schema in the entity label. Cross-schema foreign keys are normal and say nothing about ownership.

Clustered and nonclustered primary keys

A PRIMARY KEY constraint creates a unique index, clustered unless one already exists or NONCLUSTERED is stated. A table can have one clustered index, so a nonclustered primary key means some other index, or none, defines the physical order. This is physical design: the key is still the key, and the relationship still uses the same columns. The distinction changes insert and lookup behavior, not cardinality, and most diagrams correctly omit it. It is worth a note when a GUID primary key is also the clustered index.

Foreign keys: inline and table-level

SQL Server accepts an inline REFERENCES clause on a column, but scripted databases usually add constraints at the end so tables can be created in any order. SSMS also emits a WITH CHECK add followed by a separate CHECK CONSTRAINT statement, with a GO between them. The constraint exists after the add; the second statement enables it and is bookkeeping, not a second relationship.

CREATE TABLE [sales].[OrderLines] (
    [OrderId]   UNIQUEIDENTIFIER NOT NULL,
    [LineNo]    INT              NOT NULL,
    [ProductId] INT              NOT NULL,
    [Quantity]  INT              NOT NULL,
    CONSTRAINT [PK_OrderLines] PRIMARY KEY CLUSTERED ([OrderId], [LineNo])
);

ALTER TABLE [sales].[OrderLines] WITH CHECK
    ADD CONSTRAINT [FK_OrderLines_Orders]
    FOREIGN KEY ([OrderId]) REFERENCES [sales].[Orders] ([OrderId])
    ON DELETE CASCADE;
GO
ALTER TABLE [sales].[OrderLines] CHECK CONSTRAINT [FK_OrderLines_Orders];
GO
ALTER TABLE [sales].[OrderLines] WITH CHECK
    ADD CONSTRAINT [FK_OrderLines_Products]
    FOREIGN KEY ([ProductId]) REFERENCES [catalog].[Products] ([ProductId]);
GO

Both foreign keys are edges: orders to order lines, and products to order lines. The first carries ON DELETE CASCADE. The second uses the default, NO ACTION, which rejects a delete instead of propagating it. A foreign key may also be composite, declared once with two column lists; it is still one edge, with two column pairs.

Cascade actions and the multiple-path rule

The referential actions are NO ACTION (the default), CASCADE, SET NULL, and SET DEFAULT, available for both ON DELETE and ON UPDATE. SET NULL requires nullable foreign-key columns, and SET DEFAULT requires a default that satisfies the constraint. ON UPDATE CASCADE is rare only because primary keys rarely change.

SQL Server adds a restriction the other major engines do not share. A table cannot be the target of more than one cascading path. If deleting a customer cascades to orders and to addresses, and deleting an address cascades to orders, the second cascade is a second path to the same table, and SQL Server refuses the constraint with error 1785. The rule is conservative: it can reject a schema that would never cycle. It is decided at DDL time, so the script itself tells you which edges cascade.

GO is a batch separator

GO is not a T-SQL statement. It is a command understood by SSMS, sqlcmd, and other client tools, which split the script on lines containing it and send each batch separately. The server never sees it. That matters for parsing: local variables do not survive the cut, and CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER must be the first statement in their batch, so these objects sit alone between GO lines. A tool that fails on GO breaks on nearly every real script.

A generated database script typically opens with SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON, creates the schema, then tables with defaults and primary keys, then nonclustered indexes, and finally foreign keys. Constraints are often emitted in a separate section and in alphabetical rather than dependency order. Resolve names after the whole script is read, not as each statement arrives.

From script to entities and edges

• One CREATE TABLE is one entity. Schema-qualified duplicates, such as [sales].[Orders] and [staging].[Orders], are two entities.
• Each column is an attribute. NOT NULL controls optionality; IDENTITY marks a generated value; a DEFAULT is an initial value, not a relationship.
• A PRIMARY KEY, inline or named, is the entity key. A composite key keeps its column order and is still one key.
• A UNIQUE constraint or unique index is a candidate key. If it covers the foreign-key columns, the relationship is one-to-one rather than many-to-one.
• A FOREIGN KEY is an edge. The referencing table is the child, the referenced table the parent, and the cardinality is N:1 unless the child columns are unique.
• ON DELETE and ON UPDATE annotate the edge; they never create a second one.

Common mistakes

• Reading GO as a statement. It is a client-side separator; a parser that rejects it fails on almost every scripted database.
• Treating a primary key as always clustered. NONCLUSTERED is common on GUID keys, and a table has only one clustered index.
• Inferring one-to-one from a NOT NULL foreign key. Nullability says whether the relationship is optional; uniqueness says whether it is one-to-one.
• Expecting ON DELETE CASCADE to be accepted everywhere. The multiple-cascade-path rule rejects some sensible designs at DDL time.
• Collapsing NVARCHAR, DATETIMEOFFSET, and DECIMAL into generic string, timestamp, and number types.

Frequently asked questions

Why does my pasted script show tables but no relationships? The foreign keys are almost certainly in ALTER TABLE ... ADD CONSTRAINT statements at the end, after every CREATE TABLE, possibly separated by GO. A parser that only reads inline REFERENCES clauses sees no edges.

Can a table have two primary keys? No. It can have one primary key made of several columns. Two separate UNIQUE constraints are candidate keys, and only one of them is usually declared the primary key.

Should computed columns appear on the entity? Yes. They are real typed columns, and their expression is a definition, not an edge.

Try it

Paste the script into ERDGenerator; table-level constraints, schema-qualified names, defaults, and composite keys carry over to editable entities and edges.