Monday, August 3, 2009

Constraints: PK = UQ?

Another little thing I learned today, you can use uniquely constrained columns in referential integrity. Looking it up in the docs, I found this little blurb:
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_codes
(
mycode VARCHAR2(20)
CONSTRAINT pk_mycode PRIMARY KEY
);

CREATE TABLE t
(
tid NUMBER(10)
CONSTRAINT pk_tid PRIMARY KEY,
mycode
CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
CONSTRAINT nn_mycode_t NOT NULL
);
I 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).

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;

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 );
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
ADD CONSTRAINT uq_mycode
UNIQUE ( mycode );
I rebuild my tables, without referencing the new PK.
CJUSTICE@TESTING>CREATE TABLE my_codes
2 (
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
8 );

Table created.

CJUSTICE@TESTING>CREATE TABLE t
2 (
3 tid NUMBER(10)
4 CONSTRAINT pk_tid PRIMARY KEY,
5 mycode
6 CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
7 CONSTRAINT nn_mycode_t NOT NULL
8 );

Table created.
Wait a minute. I didn't change the FK to point to the PK.

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.

No comments: