Member Avatar for nathan_8

/DROP TABLE STATEMENTS/
DROP TABLE BRANCH CASCADE CONSTRAINT PURGE;
DROP TABLE SCREEN CASCADE CONSTRAINT PURGE;
DROP TABLE SESSIONS CASCADE CONSTRAINT PURGE;
DROP TABLE MOVIE CASCADE CONSTRAINT PURGE;

/CREATE TABLE STATEMENT/

CREATE TABLE BRANCH(
BRANCHID CHAR(2)NOT NULL,
BRANCHNAME VARCHAR2(20),
BRANCHSUBURB VARCHAR2(15),
BRANCHPOSTCODE CHAR(4),
CONSTRAINT BRANCH_PK PRIMARY KEY(BRANCHID));

CREATE TABLE SCREEN(
SCREENID CHAR(2) NOT NULL,
BRANCHID CHAR(2) NOT NULL,
SCREENCAPACITY NUMBER(3),
CONSTRAINT SCREEN_PK PRIMARY KEY(BRANCHID,SCREENID),
CONSTRAINT SCREEN_FK1 FOREIGN KEY(BRANCHID)REFERENCES BRANCH(BRANCHID));

CREATE TABLE MOVIE(
MOVIEID CHAR(2) NOT NULL,
MOVIENAME VARCHAR(20),
MOVIEGENRE VARCHAR(20),
MOVIECLASSIFICATION VARCHAR(5),
CONSTRAINT MOVIE_PK PRIMARY KEY(MOVIEID));

CREATE TABLE SESSIONS(
SESSIONID CHAR(4),
BRANCHID CHAR(2)NOT NULL,
SCREENID CHAR(2)NOT NULL,
MOVIEID CHAR(2)NOT NULL,
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT SESSIONS_PK PRIMARY KEY(SESSIONID),
CONSTRAINT SESSIONS_FK1 FOREIGN KEY(BRANCHID)REFERENCES BRANCH(BRANCHID),
CONSTRAINT SESSIONS_FK2 FOREIGN KEY(SCREENID)REFERENCES SCREEN(SCREENID),
CONSTRAINT SESSIONS_FK3 FOREIGN KEY(MOVIEID)REFERENCES MOVIE(MOVIEID));

can you guy help me out? i dont know why i have this error. i checked many time there seems no wrong spelling. plz show me why i have ERROR ORA - 02270.

show me how to fix it?
thank you very much

  • 3 Contributors
  • 2 Replies
  • 510 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by Alec_2

Dani AI

Generated

The error ORA-02270 means a FOREIGN KEY is referencing a column-list that is not a unique or primary key in the parent table. In this thread the root cause is that the SESSIONS table tries to reference SCREEN by a single column while SCREEN’s key is defined across two columns. Oracle requires the referenced column list to exactly match a unique or primary key on the parent (same columns, same order, compatible datatypes).

Two safe fixes:

  • Reference the composite key from SESSIONS (use both branchid and screenid):
ALTER TABLE sessions
  ADD CONSTRAINT sessions_fk_screen
  FOREIGN KEY (branchid, screenid)
  REFERENCES screen (branchid, screenid);
  • Or, if screenid is truly unique across all branches by design, make it unique in SCREEN:
ALTER TABLE screen
  ADD CONSTRAINT screen_uq_screenid UNIQUE (screenid);

Before adding a constraint: verify datatypes/lengths match exactly (e.g., CHAR(2) vs VARCHAR2(2)), confirm column order in the composite FK matches the parent, and check for orphan rows — adding a FK will fail if existing rows violate it. To find bad rows:

SELECT s.*
FROM sessions s
LEFT JOIN screen sc ON s.branchid = sc.branchid AND s.screenid = sc.screenid
WHERE sc.branchid IS NULL;

Note on earlier replies: — Oracle does support composite primary keys; the issue isn’t composite keys themselves but mismatched FK references. — a missing or non-unique parent key will trigger this error, so ensure the referenced columns form a PK or UNIQUE constraint before creating the FK.

Recommended Answers

All 2 Replies

Member Avatar for AndrisP

You cannot set combined primary key CONSTRAINT SCREEN_PK PRIMARY KEY(BRANCHID,SCREENID)
The unique index may combine two (or more) fields eg UNIQUE(BRANCHID,SCREENID)

Edited by AndrisP
Member Avatar for Alec_2

Most Probably when you have a missing Primary key is not defined from parent table. then It occurs

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.