Primary key error

Question

In Table A, i have two fields together as Primary Key. (Field1 and Field2)

In Table B, I want to refer only Field1 as FK.  Is that possible?.  I keep getting some errors.  What are the other workarounds?.

---

NSG12

Answer 1

Can you post the error message/messages?

Answer 2

If Field1 is unique you can add UNIQUE constraint and then reference. If the column is not unique then you cannot do it.

---

Plamen Ratchev

Answer 3

In Table A, Field1 and Field2 together will be Primary Key.  So, In Table B, when i refer to Field1, the error is

'The column in TableA do not match an existing primary key or unique constraint'

---

NSG12

Answer 4

--Creating composit primary key 
Create table #T( fieldid1 int not null, fieldid2 int not null, name varchar(20))
go
alter table #T 
add constraint abc primary key (fieldid1,fieldid2)


--Creating foreign key only on one column of primary key
Create table #T1( id int,Fieldid1 INT)
go
alter table #T1
add constraint fk_ foreign key (Fieldid1) references #T(fieldid1)

Hello NSG12,

 please have the code, maybe you need to modify according to your requirements.

Thanks

Answer 5

In Table A, Field1 and Field2 together will be Primary Key.  So, In Table B, when i refer to Field1, the error is

'The column in TableA do not match an existing primary key or unique constraint'

---

NSG12

In order to reference a column from the parent side in a foreign key constraint, you will need one of this conditions to be true:

- It is the primary key of the referenced table.

- There is a unique constraint by that column.

- There is a unique index by that column.

In case the foreign key constraint is multi-columns, then expand previous conditions to be multi-columns too.

AMB

Answer 6

Shez211, I tried your code, it gives the same error.  Did you not get that error?

---

NSG12

Answer 7

Hello NSG12,

Sorry i could not test the code because i don't have SQL Server at the time. Please modify your query as Hunchback mentioned above.

Thanks

Answer 8

In Table A, i have two fields together as Primary Key. (Field1 and Field2)

In Table B, I want to refer only Field1 as FK.  Is that possible?.  I keep getting some errors.  What are the other workarounds?.

You have to matchup the composite PRIMARY KEY with a corresponding composite FOREIGN KEY. This is the very basis of relational database theory. See demo following.

As indicated above if you can create a UNIQUE KEY, and you can FK reference it instead of a PRIMARY KEY.

Commonly accepted Database Design practice is to use INT IDENTITY(1,1) as SURROGATE PRIMARY KEY so there is no issue nor with composite FK referencing, neither with long, inefficient PK-s / FK-s. In such a design, you can designate the Natural Key as a UNIQUE KEY or just place a UNIQUE INDEX on the Natural Key column.

There are some applications (minority) where the Natural Key maybe a better choice for PK than INT IDENTITY Surrogate Key.

-- T-SQL composite PRIMARY KEY referencing by FOREIGN KEY
USE tempdb;
SELECT ProductID, ProductNumber, ProductName=Name
INTO Product
FROM AdventureWorks2008.Production.Product
GO
-- (504 row(s) affected)

ALTER TABLE Product 
ADD CONSTRAINT pkDouble PRIMARY KEY(ProductID, ProductNumber)
GO
-- Referencing only part of PK gives error
CREATE TABLE ProductPhoto(
ProductID int REFERENCES Product(ProductID),
Photo varbinary(max));
GO
/*
Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'Product' 
that match the referencing column list in the foreign key 
'FK__ProductPh__Produ__13F1F5EB'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
*/

-- All parts of a composite Primary Key must be referenced
CREATE TABLE ProductPhoto(
ProductID int,
ProductNumber nvarchar(25),
Photo varbinary(max));
GO

ALTER TABLE [dbo].[ProductPhoto]  WITH CHECK ADD  
CONSTRAINT [FK_ProductPhoto_Product] 
FOREIGN KEY([ProductID], [ProductNumber])
REFERENCES [dbo].[Product] ([ProductID], [ProductNumber])
GO
ALTER TABLE [dbo].[ProductPhoto] CHECK CONSTRAINT [FK_ProductPhoto_Product]
GO

DROP TABLE tempdb.dbo.ProductPhoto
DROP TABLE tempdb.dbo.Product

 

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM