I think it is clear that, given the info in your answers, and there are no Nullable columns, all five tables should be rolled into one table. That, however does not sound reasonable at all (35 "fields" is pretty much a read flag that you have a flat file, not Normalised). It would be good for you to inspect the real data, all use cases, thoroughly.
Designing databases for application requirements is not an acceptable reason for such poor design. If the column is 1::1 with the PK, then it must be in the table. And not, if it is not.
The only legitimate reason for splitting certain columns off into a separate 1::1 table is:
for Nullable columns (optional or missing values)
where the column is therefore 1::0-1 with the PK.
(Commercial DBMS automatically place [all] BOLBs off-row, on separate devices, and allow device management; for the freeware end of town, of course you have to do all that sort of basic physical optomisation yourself.)
(The small end of town cannot handle large rows either, so that's another commercially illegitimate reason that is legitimate in that case.)
Then you can support an Unique index on one of those columns:
CREATE TABLE Part (
PartId,
Description,
EtCetera -- Optional SerialNo here cannot be made Unique
)
CREATE UNIQUE CLUSTERED INDEX UC_PartId
ON Part (PartId)
CREATE TABLE PartSerial (
PartId,
SerialNo,
EtCetera
)
CREATE UNIQUE NONCLUSTERED INDEX UC_PartId
ON PartSerial (PartId)
CREATE UNIQUE CLUSTERED INDEX U_SerialNo
ON PartSerial (SerialNo) -- SerialNo can now be Unique
ALTER TABLE PartSerial ADD CONSTRAINT
FOREIGN KEY (PartId)
REFERENCES Part (PartId)
There is another mistake in the four tables. The ID column (and Unique Index) is totally and completely redundant. The pID is both the Primary Key and the Foreign Key to tblPropertyRecord.
