title: How to identify one-to-one (one-to-?) relationship using INFORMATION_SCHEMA or sys views in Sql Server
body: <p><a href="http://www.freeimagehosting.net/uploads/6e7aa06096.png" rel="nofollow noreferrer">The Problem Domain http://www.freeimagehosting.net/uploads/6e7aa06096.png</a></p>
  
  <p>So here is the problem.. Using TSQL and INFORMATION_SCHEMA or sys views, how can I identify a 1:0-1 relationship, such as FK_BaseTable_InheritedTable?</p>
  
  <p>In a concrete example, imagine a simple DTO - FK_JoinTable_ParentTable would be rendered as a collection of JoinTable on the ParentTable object, whereas FK_BaseTable_InheritedTable would either be rendered as an InheritedTable object on the BaseTable object (inheritance was a bad choice for example, I know, but not going back).</p>
  
  <p>The best I can come up with is one-to-many, same as FK_JoinTable_ParentTable. I have tried a lot of approaches, including (trying to) comparing keys and am coming up short.</p>
  
  <p>Here is the script. Problem is to, USING INFO_SCHEMA or sys views , identify FK_JoinTable_ParentTable and FK_JoinTable_Child as one-to-many and FK_BaseTable_InheritedTable as one-to-one/none.</p>
  
  <p>The litmus is being able to differentiate FK_BaseTable_InheritedTable from FK_JoinTable_ParentTable</p>
  
  <pre><code>CREATE TABLE [dbo].[Child](
   [ChildId] [int] NOT NULL,
   CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED 
  (
   [ChildId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  CREATE TABLE [dbo].[ParentTable](
   [ParentId] [int] NOT NULL,
   CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED 
  (
   [ParentId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  
  CREATE TABLE [dbo].[JoinTable](
   [PId] [int] NOT NULL,
   [CId] [int] NOT NULL,
   CONSTRAINT [PK_JoinTable] PRIMARY KEY CLUSTERED 
  (
   [PId] ASC,
   [CId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  CREATE TABLE [dbo].[InheritedTable](
   [InheritedId] [int] NOT NULL,
   CONSTRAINT [PK_InheritedTable] PRIMARY KEY CLUSTERED 
  (
   [InheritedId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  CREATE TABLE [dbo].[BaseTable](
   [BaseId] [int] NOT NULL,
   CONSTRAINT [PK_BaseTable] PRIMARY KEY CLUSTERED 
  (
   [BaseId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  ALTER TABLE [dbo].[JoinTable]  WITH CHECK ADD  CONSTRAINT [FK_JoinTable_Child] FOREIGN KEY([CId])
  REFERENCES [dbo].[Child] ([ChildId])
  
  ALTER TABLE [dbo].[JoinTable] CHECK CONSTRAINT [FK_JoinTable_Child]
  
  ALTER TABLE [dbo].[JoinTable]  WITH CHECK ADD  CONSTRAINT [FK_JoinTable_ParentTable] FOREIGN KEY([PId])
  REFERENCES [dbo].[ParentTable] ([ParentId])
  
  ALTER TABLE [dbo].[JoinTable] CHECK CONSTRAINT [FK_JoinTable_ParentTable]
  
  ALTER TABLE [dbo].[BaseTable]  WITH CHECK ADD  CONSTRAINT [FK_BaseTable_InheritedTable] FOREIGN KEY([BaseId])
  REFERENCES [dbo].[InheritedTable] ([InheritedId])
  
  ALTER TABLE [dbo].[BaseTable] CHECK CONSTRAINT [FK_BaseTable_InheritedTable]
  </code></pre>