View previous topic :: View next topic
|
Author |
Message |
Raj mishra
New User
Joined: 22 Jan 2007 Posts: 2 Location: hyderabad
|
|
|
|
in my table1 there are 3(three) primary key and in my second i want use one prmary key as foregn key. plz give me right path
table 1
*********************************************
CREATE TABLE PINFO(CUSID NUMERIC(10) NOT NULL,CUSNAM CHAR(20)
NOT NULL,DOB CHAR(10) NOT NULL,OCUP CHAR(20) NOT NULL,
LOC CHAR(20) NOT NULL,CITY CHAR(18) NOT NULL,
STATE CHAR(15) NOT NULL,PIN NUMERIC(6) NOT NULL,
PHONE NUMERIC(11) NOT NULL,MOBILE NUMERIC(10) NOT NULL,
EMAIL CHAR(30) NOT NULL,
PRIMARY KEY(CUSID,PHONE,MOBILE,EMAIL))
IN SSSGENDB.SSSGENTS;
Table=2
*************************************************
CREATE TABLE ACINFO(ACTNO NUMERIC(16) NOT NULL, 00160023
CUSID NUMERIC(10) NOT NULL,ACTYPE CHAR(1) NOT NULL, 00170023
OPNBAL NUMERIC(10) NOT NULL, 00190024
OPNDAT CHAR(11) NOT NULL,PRIMARY KEY(ACTNO), 00200024
FOREIGN KEY(CUSID) REFERENCES PINFO(CUSID)) 00220024
IN SSSGENDB.SSSGENTS; 00230024
-------+---------+---------+---------+---------+---------+---------+---------+
NT408I SQLCODE = -538, ERROR: FOREIGN KEY CUSID DOES NOT CONFORM TO THE
DESCRIPTION OF A PARENT KEY OF TABLE SSS070.PINFO
SQLSTATE = 42830 SQLSTATE RETURN CODE
SQLERRP = DSNXICKK SQL PROCEDURE DETECTING ERROR
SQLERRD = 160 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
SQLERRD = X'000000A0' X'00000000' X'00000000' X'FFFFFFFF' |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
I believe you have your definitions "reversed".
The components of your compound key might refer to the keys of (an)other table(s), but i do not believe your ACINFO table may have a foreign key that is only part of the PINFO key. |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
in my table1 there are 3(three) primary key
Major correction: A table can contain one and only one primary key.
I guess you wanted to say that the PK is built on 3 columns. |
|
Back to top |
|
|
Raj mishra
New User
Joined: 22 Jan 2007 Posts: 2 Location: hyderabad
|
|
|
|
hello,
in a Table three field contain primary key and i use one filed as a foreign key in another table. is it possible or not?
hope i will get help |
|
Back to top |
|
|
devidas-patil
New User
Joined: 08 Jan 2007 Posts: 54 Location: pune
|
|
|
|
yes
i think it is possible to use one of the column of the composite key as foreign key in another table. |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
It is not possible. Parent key should be either a unique key or a Primary key.
In your case, PINFO.CUSID could have duplicate values. It is a part of Pk. You cannot build this relation ship in order to enfore referentail constraints. |
|
Back to top |
|
|
|