Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.Not really hard to miss I guess.
The only reason I found this was because I made the changes to the codes table, putting a UNIQUE constraint on the former PK, and nothing broke when I rebuilt the database.
So here are my 2 tables:
CREATE TABLE my_codesI want to add a surrogate key to the MY_CODES table, per this discussion. I made a compromise, essentially anything that will or could be user entered will have a surrogate key (either SYS_GUID or sequence generated).
CONSTRAINT pk_mycode PRIMARY KEY
CREATE TABLE t
CONSTRAINT pk_tid PRIMARY KEY,
CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
CONSTRAINT nn_mycode_t NOT NULL
My first step was to remove the constraint on T, then drop the PK constraint on MY_CODES, add a new column that will hold the surrogate key and finally add the PK constraint to the new column.
ALTER TABLE t DROP CONSTRAINT fk_mycode_t;Since I'm changing the meaning, I want to make sure that someone doesn't enter the same code twice, so I add a UNIQUE constraint on the table.
ALTER TABLE my_codes DROP CONSTRAINT pk_mycode;
ALTER TABLE my_codes ADD ( mycodeid NUMBER(10) );
ALTER TABLE my_codes
ADD CONSTRAINT pk_mycodeid
PRIMARY KEY ( mycodeid );
ALTER TABLE my_codesI rebuild my tables, without referencing the new PK.
ADD CONSTRAINT uq_mycode
UNIQUE ( mycode );
CJUSTICE@TESTING>CREATE TABLE my_codesWait a minute. I didn't change the FK to point to the PK.
3 mycodeid NUMBER(10)
4 CONSTRAINT pk_mycodeid PRIMARY KEY,
5 mycode VARCHAR2(20)
6 CONSTRAINT uq_mycode UNIQUE
7 CONSTRAINT nn_mycode NOT NULL
CJUSTICE@TESTING>CREATE TABLE t
3 tid NUMBER(10)
4 CONSTRAINT pk_tid PRIMARY KEY,
6 CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
7 CONSTRAINT nn_mycode_t NOT NULL
I then posted the finding on Twitter and received a timely response from @neilkod:
So what's the point? There isn't one. I just found it interesting.