Hello,
I'm setting up my tables and have run into a situation using a composite key:
JoinTable1 - this table has several more FK's, and other fields.
JT_ID, INT, Identity
FK1
FK2
All three make up a composite key, as it seems that this is the minimal requirement for uniqueness.
JoinTable2
JT_ID_FK ----this references JoinTable1 ID
FK3
In Access, I can simply create a relationship between JT_ID and JT_ID_FK, even though those three fields are marked as the Primary Key.
In SQL Server, I am having a hard time figuring out how to create a FK reference in JoinTable2. I have read that I would need all three fields from JoinTable1 in JoinTable2, but in creating a FK reference using T-SQL, I get error messages.
I added those three fields into JoinTable2, and referenced them individually to JoinTable1, but that doesn't work.
So far I haven't found anything concrete on the Web.
Thanks for any help.
Actually, an identity value should always be unique by itself. You'd have to explicitly take complex steps to force a duplicate identity value.
1 Like
Right, I got that. The issue I'm struggling with is using a 3 column composite key as a foreign key in another table. In the example would be JoinTable2.
This shows how I have it set up at the moment in MS Access. Would this work in SQL Server, referencing the only AASJoinID in the H_AAS_PN_Join table, (described as JoinTable2 above)?
See again what Scott Pletcher said. Regardless of whether it "works" in Access, having a 3-field composite primary key in which one field is an Identity field makes no sense.
How would you set up this design? From AS02 to the Part, each can have a quantity of more than one.
I have around 1,000 pages of parts lists, and this is what I've found how the OEM has organized it.
It really isn't clear what you are trying to do...in your Access database you have the table H_AASJoin that has 3 columns defined as the primary key. You have the table H_AAS_PN_Join which has 2 columns defined as the PK.
The relationship between these 2 tables is based on the column AASJoinID -> AAS_JoinID_FK. In other words, they are joined based on the first column of the PK in the table H_AAS_PN and not on the full PK from that table.
The second column in the H_AAS_PN_Join tables PK is a FK to the H_PartNumbers table. This shows that you can have only 1 row in this join table per AAS_JoinID and PNID_FK combination. In other words - a part number can only be specified one time for all combinations of:
AASJoinID
AssetID_FK
Assem01_FK
If the column definition in the table H_AAS_PH_Join for the column AAS_JoinID_FK is actually a combination of the 3 columns in the related table - then Access is hiding the actual relationship. Not sure if that is the case.
To your question - in SQL Server you would need to add the other columns so that you would have 4 columns in the H_AAS_PH_Join table. You can then create the PK on that table using those 4 columns and define a FK relationship to the other table based on the 3 columns coming from the other table.
Well, Access let me create a relationship using AASJoinID, and allowed referential integrity. I don't know how that works.
As far as part numbers go, I do not want duplicate parts entries allowed for that particular combination of equipment. For instance:Asset - 1, Assem01 - 1, Assem02 - 1, Assem03 - 2, Assem04 - 21 and PN - 3. So the qualified assembly, I suppose, would be: 1, 1, 1, 2, 21, (PN)3. The next could be 1, 1, 1, 2, 21, (PN)4.
What if I use an auto number ID as the PK, in H_AASJoin, and use that as the FK in the second table? Then, to make sure that I don't have duplicate combinations of the rest of the FKs in AASJoin, how do I accomplish that?
I just realized that I cannot have duplicate PN values for the combination of every FK in the AASJoin table. Perhaps a better way to handle this would be on the front end?
You can setup a surrogate key as an identity - but you would also need a unique key across the columns that uniquely identify the row.
Not sure I would setup that identity as the clustering key (clustered index). That will depend on how the table is generally queried - you want to review the usage to determine the best clustered index which may be the 3 column unique key - the identity - or some other column or columns.
I'm not quite familiar with surrogate key, yet, but I did discover the UNIQUE clause, which would allow me to create a four or five column unique 'key', not to be used as the Pkey, I would just use the identity column. Is that what you are suggesting?
Edit:::
Question: this is the code to create table AASJoin:
CREATE TABLE [dbo].[H_AASJoin](
[AASJoinID] [int] IDENTITY(1,1) NOT NULL,
[AssetID_FK] [int] NOT NULL,
[AS01_FK] [int] NOT NULL,
[AS02_FK] [int] NOT NULL,
[AS02_Qty] [int] NOT NULL,
[AS03_FK] [int] NOT NULL,
[AS03_Qty] [int] NOT NULL,
[AS04_FK] [int] NOT NULL,
[AS04_Qty] [int] NOT NULL,
CONSTRAINT [PK_AASJoin] PRIMARY KEY CLUSTERED (
[AASJoinID] ASC,
[AssetID_FK] ASC,
[AS01_FK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Is it possible to reference the constraint name as a FK in the other table? I don't think it is, but want to ask anyway.
Edit:: Just changed the PKey constraint to AASJoinID, and added this:
ALTER TABLE [dbo].[H_AASJoin] ADD CONSTRAINT [AAS_Unique] UNIQUE (AssetID_FK, AS01_FK, AS02_FK, AS03_FK, AS04_FK)
