Sql-server – Query For All Constraints in a SQL Database Diagram

check-constraintsconstraintdatabase-diagramssql server

Is there a way to show constraints in a SQL Server db diagram?

Like for example, in a card number, it shows there is a CHECK constraint etc..

Best Answer

In SSMS, if you expand the tree of tables for your database, then expand the Columns folder, the icon next to a column will be a silver/gray key if it's part of a foreign key relationship, but unfortunately it won't show you the column to which column it's mapped.

You could also create a new diagram in SSMS by right-clicking the "Database Diagrams" folder underneath your database in the tree and choosing "New Database Diagram." You will get lines between the tables where foreign keys exists.

You could also use third-party tools to reverse engineer a diagram from your DB schema, like Microsoft Visio or Sparx Enterprise Architect.

I'm not sure SSMS Express supports these things, so you might be out of luck with anything fancy/visual.

Related Solutions

Sql-server – Trusted Constraints and NOT FOR REPLICATION

NOT FOR REPLICATION was indended for this scenario:

You have a Order table with a FK to Person table.
Order table is in publication no.1 and Person table is in publication no.2.
The Order pub and Person pub will happen at different times. Therefore if some new records were in the Order table were replicated down to the subscriber and the Person table was missing the person that new Order was for, you don't want the replication insert to fail at the subscriber. Instead, it can be trusted that the new Person record will come down at some point because the FK was used at the Publisher.
At the subscriber, if you insert an Order record, the FK will check that the Person record exists.

I would always stick with this policy and use NOT FOR REPLICATION only for that purpose of FK's relating to tables in different publications, and not for the purpose of query plan optimisation.

Sql-server – SQL Server 2008 R2 – How to check Foreign Key constraints in a transaction only when committed

No, server checks Foreign keys for every statement inside transaction

The way is to disable all the FKeys in target DB just after the beginning of transaction and enable them back just before commit.

Useful script:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + N'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id)+N'].['+OBJECT_NAME(fk.parent_object_id)+N'] NOCHECK CONSTRAINT ['+fk.NAME+N'];'+NCHAR(13)+NCHAR(10)
FROM sys.foreign_keys fk

PRINT @sql

Best Answer

Related Solutions

Sql-server – Trusted Constraints and NOT FOR REPLICATION

Sql-server – SQL Server 2008 R2 – How to check Foreign Key constraints in a transaction only when committed

Related Question