> That is, the UNIQUE constraint needs to have A1_CAT removed from it.
Or, (just adding the obvious), the Foreign Key reference need to refer to both fields (in the same order) to make the Unique index valid.
@OP: I'll try put in words what Keith assumed was understood - It's not about the Index mechanism, it's about the referential integrity - meaning:
If a table has 2 columns A and B and a unique/PK constraint UNIQUE(A,B),
then it cannot have duplicates of A,B, but it CAN have duplicates of A, so that:
```
No. A | B
---|---
1 1 | Z
2 1 | Y
3 2 | Z
```
etc. is possible.
If a foreign key was possible on the A of UNIQUE(A,B) for a child table, then a child record with value 1 for A would technically have 2 parents (Rows 1 and 2 where A=1) which violates the very core of a Foreign-Key relationship.
If the child (c) however contains 2 columns, say A' and B' which map directly to A and B in the parent (p), then the foreign-key can be created on c(A',B') REFERENCES p(A,B) - which conforms to the Unique index and so the integrity is restored and everything works.
What your definition should look like to work (though I don't know if it fulfills your layout desires):
```
CREATE TABLE A1(
A1_INDEX INTEGER,
A1_CAT VARCHAR(1) DEFAULT 'W',
CONSTRAINT A1_PK PRIMARY KEY (
A1_INDEX,
A1_CAT
)
);
CREATE TABLE C1(
C1_INDEX INTEGER,
C1_CAT VARCHAR(1)
CONSTRAINT C1_INDEX_FK FOREIGN KEY (
C1_INDEX,
C1_CAT
)REFERENCES A1(
A1_INDEX
A1_CAT
)
);
```
If, on the other hand, you can only have 1 child in C1 for every single INDEX in A1, then you need this simpler layout (as Keith suggested):
```
CREATE TABLE A1(
A1_INDEX INTEGER,
A1_CAT VARCHAR(1) DEFAULT 'W',
CONSTRAINT A1_PK PRIMARY KEY (
A1_INDEX,
A1_CAT
),
CONSTRAINT UIdx UNIQUE(A1_INDEX)
);
CREATE TABLE C1(
C1_INDEX INTEGER REFERENCES A1(A1_INDEX)
);
```
I'll further add here that this layout is silly as if A1_INDEX is already Unique... there is no point to the PK including A1_CAT... you'd probably be better off making A1_INDEX the PK and adding UNIQUE(A1_INDEX,A1_CAT).
I hope that removes some questions.
Good luck!