CODEWITHSUNDEEP
sqloracle-databaseforeign-keysprimary-keycomposite-primary-key
Atheya
Atheya

Reputation: 99

Adding composite primary and foreign keys to three columns in one table

QUESTION: Create a table named REP_CONTRACTS containing the columns listed in the following chart. A composite PRIMARY KEY constraint including the REP_ID, STORE_ID, and QUARTER columns should be assigned. In addition, FOREIGN KEY constraints should be assigned to both the REP_ID and STORE_ID columns.

create table REP_CONTRACTS
(       STORE_ID    number(5) not null  
    ,   NAME        number(5)   
    ,   QUARTER     char(3)   not null      
    ,   REP_ID      number(5) not null  
);



alter table REP_CONTRACTS
add           constraint  FK_ID_STORE                foreign key(STORE_ID)
reference REP_CONTRACTS(STORE_ID)
add           constraint  FK_ID_REP                  foreign key(REP_ID)
reference REP_CONTRACTS(REP_ID) 
add           constraint  PK_REP_CONTRACTS           primary key(STORE_ID, REP_ID, QUARTER) 
reference REP_CONTRACTS(REP_ID, STORE_ID, QUARTER)
;

Even with out the "not null", I still get the same result. I have tried adding the primary and foreign keys with and without the references and constraint but I always keep getting this as a result.

Error starting at line : 618 in command -
alter table REP_CONTRACTS
add           constraint  FK_ID_STORE                foreign key(STORE_ID)                      
reference REP_CONTRACTS(STORE_ID)
add           constraint  FK_ID_REP                  foreign key(REP_ID)                        
reference REP_CONTRACTS(REP_ID)  
add           constraint  PK_REP_CONTRACTS           primary key(STORE_ID, 
REP_ID, QUARTER)     reference REP_CONTRACTS(REP_ID, STORE_ID, QUARTER)
Error report -
ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:

Upvotes: 1

Views: 2435

Answers (2)

UnsafeMango
UnsafeMango

Reputation: 1

Create your table and add a composite primary key that consists of rep_id, store_id and quarter and then add two foreign keys (rep_id and store_id). You can use the Table level method to create the table and add the constraints in one command.

CREATE TABLE REP_CONTRACTS(
Store_ID NUMBER(8),
Name NUMBER(5),
Quarter CHAR(3),
Rep_ID NUMBER(5),
CONSTRAINT rep_contracts_composite_pk PRIMARY KEY (Rep_ID, Store_ID, Quarter),
CONSTRAINT rep_contracts_Store_ID_fk FOREIGN KEY (Store_ID)
    REFERENCES BOOK_STORES(Store_ID),
CONSTRAINT rep_contracts_Rep_ID_fk FOREIGN KEY (Rep_ID)
    REFERENCES store_reps(Rep_ID)
);

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

You should define your primary key( there may exist once per table ) or unique keys firstly, and use keyword references instead of reference like in the following :

alter table REP_CONTRACTS add constraint  PK_REP_CONTRACTS primary key(STORE_ID, REP_ID, QUARTER);
alter table REP_CONTRACTS add constraint  PK_REP_CONTRACTS_ST unique(STORE_ID);
alter table REP_CONTRACTS add constraint  PK_REP_CONTRACTS_REP unique(REP_ID);

alter table REP_CONTRACTS add constraint  FK_ID_STORE  foreign key(STORE_ID) references REP_CONTRACTS(STORE_ID);
alter table REP_CONTRACTS add constraint  FK_ID_REP foreign key(REP_ID) references REP_CONTRACTS(REP_ID); 
alter table REP_CONTRACTS add constraint  FK_ID_REP_ST_QU foreign key(REP_ID, STORE_ID, QUARTER) references REP_CONTRACTS(REP_ID, STORE_ID, QUARTER);

Upvotes: 1

Related Questions