If I'm understanding everything right, the uniqueness is only checked within each partition because only the partitions can have unique indexes. If you're partitioning by month and got an id field that needs to be unique to the table, you could conceivably have the same id show up in different months. Just because I partition by months doesn't mean I don't sometimes need the whole table, too. I might usually only need monthly data, but sometimes I might need a full history. Now if I've got a duplicate I'm fucked and I don't even know it.

Foreign keys referencing partitioned tables are not supported. Joins are absolutely fine.

No, it means you might have duplicates of fields you don't want duplicates of. In general, JOINs work much more efficiently when there's an index on the columns used to join the tables, but they're not required.

Let's say you want a table for your account transactions:

  create table transaction (
    tran_id int primary key not null,
    tran_date timestamp(0) not null,
    account int not null,
    amount decimal(30,4) not null
  );

Now if there's already a transaction with an id of 8043, you can't insert another transaction with that same id. There's only one transaction with 8043 allowed in the whole table. However, if we partition the table:

  create table transaction (
    tran_id int not null,
    tran_date timestamp(0) not null,
    account int not null,
    amount decimal(30,4) not null
  ) partition by range (tran_date);

  create table transaction_y2018m01 partition of transaction
  for values from ('2018-01-01 00:00:00') to ('2018-01-31 23:59:59');

  create table transaction_y2018m02 partition of transaction
  for values from ('2018-02-01 00:00:00') to ('2018-02-28 23:59:59');

  alter table transaction_y2018m01 add constraint ux_transaction_y2018m01_tran_id unique (tran_id);
  alter table transaction_y2018m02 add constraint ux_transaction_y2018m02_tran_id unique (tran_id);

See, the only uniqueness restrictions are on the partitions, not the overall table. Now I could potentially have a transaction with an id of 8043 in both January and February of 2018, as well as any number of transactions with an id of 8043 not in either of those two months. If my application assumes that transaction ids are always unique, that's got the potential to cause a problem. If multiple applications or multiple users use the same database, it's possible that an error or a race condition might cause a duplicate id.

The question is enforcement, bit generation.