CODEWITHSUNDEEP
sqlderby
carlspring
carlspring

Reputation: 32617

Table in Derby with complex primary key

Is it possible to have a primary key composed of foreign keys fk1, fk2, fk3

Where:

I have the following SQL:

CREATE TABLE role_mappings (
       ROLE_NAME            VARCHAR(64)       NOT NULL,
       NESTED_ROLE_NAME     VARCHAR(64)       DEFAULT NULL,
       PRIVILEGE_NAME       VARCHAR(64)       DEFAULT NULL,
       PRIMARY KEY (ROLE_NAME, NESTED_ROLE_NAME, PRIVILEGE_NAME),
       CONSTRAINT fk_access_roles_rm_1 FOREIGN KEY (ROLE_NAME) REFERENCES access_roles (ROLE_NAME),
       CONSTRAINT fk_access_privileges FOREIGN KEY (PRIVILEGE_NAME) REFERENCES access_privileges(PRIVILEGE_NAME),
       CONSTRAINT fk_access_roles_rm_2 FOREIGN KEY (NESTED_ROLE_NAME) REFERENCES access_roles(ROLE_NAME)
);

When I insert NULL into NESTED_ROLE_NAME or PRIVILEGE_NAME, I get:

Column 'NESTED_ROLE_NAME'  cannot accept a NULL value.

What's the proper way of doing this?

Upvotes: 2

Views: 2060

Answers (1)

Tin Bum
Tin Bum

Reputation: 1491

Primary key values must be unique. If you allow null values into these fields that makeup the primary key you will quickly run into trouble with non-uniqueness. AVOId using null values would be my advice.

Upvotes: 2

Related Questions