I created the table, then later created the foreign key via constraints.
Example:
- tb_DecodeHeaders (PK: DecodeNumber)
- tb_Decode (PK: DecodeID; FK: DecodeNumber)
- tb_Equip_Armor (PK: ArmorID; FK: DecodeID)
I manually created the tables above, then altered the last 2 tables with foreign key constraints.
I originally did NOT have a DecodeID in the tb_Decode, but rather 2 primary keys (DecodeNumber, DecodeValue), but then I manually added a DecodeID primary key instead.
Before I did this, however, joins always worked out (see below example)
I have many tables referencing the tb_Decode and I simply joined to the table with a WHERE clause that separated out the header.
NOTE: Maybe it's better to have a separate tb_ArmorCategory table, but I have so many categories that I put them all in the Decode table instead.
Example:
Code:
SELECT
ArmorName,
(SELECT DecodeDescription
FROM tb_Decode d inner join tb_Equip_Armor a
ON d.DecodeValue = a.ArmorCategory
WHERE d.DecodeNumber = 4) AS ArmorCategory
FROM tb_Equip_Armor
That was BEFORE I added the DecodeID as a PK instead.
=================================
When I select SCRIPT PROCEDURE AS CREATE TABLE in SQL, I get the code below (even though I manually created the table and later added the foreign key constraint). Similar scripting exists for the Armor Table (with the ArmorCategory as a foreign key linking to the tb_Decode (DecodeID) column).
However, I did not add any diagram. And when I created the Data model, these 3 tables were not joined together in the graphic display (ie: no arrow showing any relationship).
What part of your scripting example should I have added to mine to make this happen?
Code:
/****** Object: Table [dbo].[tb_Decode] Script Date: 4/3/2018 8:54:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Decode](
[DecodeID] [int] IDENTITY(1,1) NOT NULL,
[DecodeNumber] [int] NOT NULL,
[DecodeValue] [nchar](10) NOT NULL,
[DecodeDescription] [nvarchar](50) NULL,
[DecodeSort] [int] NULL,
CONSTRAINT [PK_tb_Decode_1] PRIMARY KEY CLUSTERED
(
[DecodeID] 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].[tb_Decode] WITH CHECK ADD CONSTRAINT [FK_tb_Decode_tb_DecodeHeaders] FOREIGN KEY([DecodeNumber])
REFERENCES [dbo].[tb_DecodeHeaders] ([DecodeNumber])
GO
ALTER TABLE [dbo].[tb_Decode] CHECK CONSTRAINT [FK_tb_Decode_tb_DecodeHeaders]
GO
Chapter 14 - Mapping Data Model (page 489)
button below this post to show your appreciation!
