| Constraint question. [message #372460] |
Wed, 14 February 2001 05:31  |
Jonas Blomqvist
Messages: 3
Registered: February 2001
|
Junior Member |
|
|
Hello,
I'm quite new to SQL and haven't figured out how to set up a CONSTRAINT.
Here's the scenario with SQL:
CREATE TABLE FB_S_1 (
S_1 NUMBER NOT NULL,
S_2 NUMBER NOT NULL,
S_3 NUMBER,
S_4 NUMBER,
S_5 NUMBER,
S_6 NUMBER,
S_7 NUMBER,
S_8 NUMBER,
S_9 NUMBER,
PRIMARY KEY ( S_1, S_2 )
);
COMMENT ON COLUMN FB_S_1.S_2 IS 'The comment';
/* Create table */
CREATE TABLE FB_U_1 (
U_1 NUMBER NOT NULL,
U_2 VARCHAR2(32) NOT NULL,
U_3 VARCHAR2(32),
U_4 VARCHAR2(32),
U_5 VARCHAR2(64),
/* Set primary keys */
PRIMARY KEY ( U_1, U_2 )
);
/* Unique key */
CREATE UNIQUE INDEX U_2_PK ON
FB_U_1(U_2);
/* Sequence to update ID for new field with */
CREATE SEQUENCE U_1_SEQ START WITH 1;
/* Create trigger to launche when inserting new record */
CREATE OR REPLACE TRIGGER U_1_TRG
BEFORE INSERT ON FB_U_1
FOR EACH ROW
BEGIN
SELECT U_1_SEQ.NEXTVAL INTO :NEW.U_1
FROM DUAL;
END;
/* Here is the problem ! */
ALTER TABLE FB_S_1 ADD CONSTRAINT FB_S_1_FK1
FOREIGN KEY (S_1)
REFERENCES FB_U_1 (U_1);
It's this last clause I cant get to work. I'll get the error: "ORA-02270: no matching unique or primary key for this column-list"
Please, any help appriciated !
Regards, Jonas
|
|
|
|
| Re: Constraint question. [message #372479 is a reply to message #372460] |
Wed, 14 February 2001 12:37  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
This means that the child table has values in S_1 which do not appear in FB_U_1 (U_1);
Solution :
select u_1 from FB_U_1
minus
select s_1 from FB_S_1;
to show which values are causing the problem.
You might like to consider giving your tables and columns more meaningful names. Also, name the Primary key constraints in the same you you name a foreign key constraint, else Oracle will assign a system generated one like SYS_001234.
You can remove the primary key from the table creation, and use an "alter table" like with the FK.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
