Is a composite key correct here? I have two tables, Lpn and Uld that will relate on one column, that is, UldNo.

To give some background on the issue 

An Lpn is a label that is assigned to one or more pieces of freight in a warehouse. A Uld is a pallet that the freight is built on so that it can go on an airplane. Every Uld has a unique identifier on it, consisting of a 3 letter type, 5 digit serial number, and 2 letter suffix that indicates the owner of the pallet. A Uld can come through a warehouse multiple times, and each time would have different Lpns (freight) on it.

To ask about the database 

An Lpn row will only relate to one Uld row, and a Uld row may relate to multiple Lpn rows. The issue comes along is that UldNo cannot be the sole PRIMARY KEY (PK) for Uld because a Uld may be reused later (with different Lpns), so I created a PK of ID. If I try to create the relationship, I get an error because UldNo is not a PK. From everything I've learned, composite keys should be avoided. Is it correct to use a composite key in this situation? And if so, do I need to create the ID column in the Lpn table to create the relationship?

The following assumptions are correct:


On each flight, multiple pallets are transported.
Each pallet contains multiple LPNs (labels)
Pallets get reused.
LPNs (labels) are NOT reused.


The DBMS I'm using is SQL Server 2016 Express.
Maybe the following approach would be of help (the parent tables Uld and Flight should be created first; test server running PostgreSQL 9.3)

create table Uld ( uldno varchar(10) primary key);

create table flight (flightid varchar(32) primary key);

create table LPN (
  id integer primary key
, uldno varchar(10) references Uld(uldno)
, flight varchar(32) references Flight(flightid)
, unique (uldno, flight)
);


Test data:

insert into Flight values ('A'),('B'),('C'),('D');

insert into Uld values ('AAA12345ef'),('BBB12345gh')
, ('CCC12345ij'),('DDD12345kl');

insert into LPN values 
(1, 'AAA12345ef' ,'A'),
(2, 'AAA12345ef' ,'B'),
(3, 'AAA12345ef' ,'C'),
(4, 'BBB12345gh' ,'A'),
(5, 'BBB12345gh' ,'D'),
(6, 'BBB12345gh' ,'C'),
(7, 'CCC12345ij' ,'A'),
(8, 'CCC12345ij' ,'B'),
(9, 'CCC12345ij' ,'C'),
(10, 'DDD12345kl' ,'A');


We get (something like):

+------------+
| table: Uld |
+------------+
| uldno [PK] |
+------------+
|AAA12345ef  |
|BBB12345gh  |
|CCC12345ij  |
|DDD12345kl  |
+------------+

+-------------------------------+
| table: LPN                    | 
+-----------------+-------------+
| id | uldno [FK] | flight [FK] |
+-----------------+-------------+ 
|1   | AAA12345ef |A            |
|2   | AAA12345ef |B            |
|3   | AAA12345ef |C            |
|4   | BBB12345gh |A            |
|5   | BBB12345gh |D            |
|6   | BBB12345gh |C            |
|7   | CCC12345ij |A            |
|8   | CCC12345ij |B            |
|9   | CCC12345ij |C            |
|10  | DDD12345kl |A            |
+-----------------+-------------+

+---------------+
| table: Flight |
+---------------+
| flightid [PK] |
+---------------+
|A              |
|B              |
|C              |
|D              |
+---------------+


Testing:

-- eg which Ulds are on flight A?
select uldno
from LPN 
where flight = 'A';


The data types for LPN.id and Flight.flightid should be adjusted as per requirements.  This layout should also work if there are several LPNs per uldno (if they have the same flightid in the LPN table).
It may aid your understanding of "keys" if you (also) regard them as "constraints" (~ rules that define certain limitations see eg https://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT322).