Tuesday, June 23, 2015

the classic MSSQL bridge table

For many-to-many joins...

---$ Create table dbo.MyBridge
IF OBJECT_ID(N'dbo.MyBridge') IS NULL
BEGIN
   PRINT 'Create table dbo.MyBridge'
   CREATE TABLE dbo.MyBridge
   (
      MyBridgeId int IDENTITY NOT NULL,
      NorthShoreId int NOT NULL,
      SouthShoreId int NOT NULL
   ) ON [PRIMARY]
   ALTER TABLE dbo.MyBridgeId ADD CONSTRAINT
      PK_MyBridgeId PRIMARY KEY CLUSTERED
      (
         MyBridgeIdId
      ) WITH (
         STATISTICS_NORECOMPUTE = OFF,
         IGNORE_DUP_KEY = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
   ALTER TABLE dbo.MyBridgeId ADD CONSTRAINT
      FK_MyBridge_To_NewEngland FOREIGN KEY
      (
         NorthShoreId
      ) REFERENCES dbo.NewEngland (
         NewEnglandId
      ) ON UPDATE NO ACTION ON DELETE NO ACTION
   ALTER TABLE dbo.MyBridgeId ADD CONSTRAINT
      FK_MyBridgeId_To_Texas FOREIGN KEY
      (
         SouthShoreId
      ) REFERENCES dbo.Texas (
         TexasId
      ) ON UPDATE NO ACTION ON DELETE NO ACTION
END
GO

 
 

Addendum 5/7/2016: I am realizing that what is above is messed up but I'm just gonna let it be. It makes sense to me. ;)

No comments:

Post a Comment