Home » SQL & PL/SQL » SQL & PL/SQL » Having a problem with foreign key when creating table
| Having a problem with foreign key when creating table [message #35951] |
Thu, 25 October 2001 08:37  |
Steven
Messages: 13
Registered: May 1999
|
Junior Member |
|
|
I'm a student having a difficulty with creating a table using SQL*Plus. Somehow I couldn't create a composite foreign key in 1 table to reference to the primary key in another. Below is the scrip. Could somebody help. thanks
CREATE TABLE SIS_STUDENT
(STUDENT_NO NUMBER (3)
CONSTRAINT SS_SNO_PK PRIMARY KEY
CONSTRAINT SS_SNO_CHK_RANGE CHECK (STUDENT_NO BETWEEN 1 AND 999),
LAST_NAME VARCHAR(20)
CONSTRAINT SS_LN_CHK_UPP CHECK (LAST_NAME = UPPER(LAST_NAME))
CONSTRAINT SS_LN_CHK_NN NOT NULL,
FIRST_NAME VARCHAR(20)
CONSTRAINT SS_FN_CHK_UPP CHECK (FIRST_NAME = UPPER(FIRST_NAME))
CONSTRAINT SS_FN_CHK_NN NOT NULL,
SEX_CODE CHAR(1)
CONSTRAINT SS_SC_CHK_UPP CHECK (SEX_CODE = UPPER(SEX_CODE))
CONSTRAINT SS_SC_CHK_CHAR CHECK (SEX_CODE IN ('M','F')),
BIRTH_DATE DATE,
AREA_CODE NUMBER(3),
PHONE_NUMBER NUMBER(7),
ADDRESS1 VARCHAR(25),
ADDRESS2 VARCHAR(25),
CITY VARCHAR(12),
PROVINCE CHAR(2),
POSTAL_CODE CHAR(6)
);
CREATE TABLE SIS_COURSE
(COURSE_NO CHAR(5),
COURSE_SEG CHAR(1)
CONSTRAINT SC_CS_CHK_RANGE CHECK (COURSE_SEG IN ('A','B','C','D','E')),
CONSTRAINT SC_CNCS_PK PRIMARY KEY (COURSE_NO,COURSE_SEG),
COURSE_DESC VARCHAR(15)
);
CREATE TABLE SIS_TEACHER
(TEACHER_NO NUMBER(3)
CONSTRAINT ST_TNO_PK PRIMARY KEY
CONSTRAINT ST_TNO_CHK_RANGE CHECK (TEACHER_NO BETWEEN 100 AND 999),
LAST_NAME VARCHAR(20)
CONSTRAINT ST_LN_CHK_UPP CHECK (LAST_NAME = UPPER(LAST_NAME))
CONSTRAINT ST_LN_CHK_NN NOT NULL,
FIRST_NAME VARCHAR(20)
CONSTRAINT ST_FN_CHK_UPP CHECK (FIRST_NAME = UPPER(FIRST_NAME))
CONSTRAINT ST_FN_CHK_NN NOT NULL,
SIN NUMBER(9),
CONSTRAINT ST_SIN_UK UNIQUE(SIN),
ANNUAL_SALARY NUMBER(5,2),
COMPUTER_ID CHAR(5),
CONSTRAINT ST_COMP_ID_UK UNIQUE(COMPUTER_ID)
);
CREATE TABLE SIS_TEACH_COURSE
(COURSE_DAY NUMBER(1)
CONSTRAINT SS_CD_CHK_RANGE CHECK (COURSE_DAY BETWEEN 0 AND 6),
COURSE_PERIOD NUMBER(1)
CONSTRAINT SS_CP_CHK_RANGE CHECK (COURSE_PERIOD BETWEEN 1 AND 8),
COURSE_NO CHAR(5),
COURSE_SEG CHAR(1),
CONSTRAINT SS_CNCS_FK FOREIGN KEY (COURSE_NO,COURSE_SEG) REFERENCES SIS_COURSE(COURSE_NO,COURSE_SEQ),
);
----------------------------------------------------------------------
|
|
|
|
| Re: Having a problem with foreign key when creating table [message #35955 is a reply to message #35951] |
Thu, 25 October 2001 09:39  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Your last statement had a misspelled column name (COURSE_SEQ instead of COURSE_SEG) and an extra comma before the last parentheses.
create table SIS_TEACH_COURSE
(COURSE_DAY number(1)
constraint SS_CD_CHK_RANGE check (COURSE_DAY between 0 and 6),
COURSE_PERIOD number(1)
constraint SS_CP_CHK_RANGE check (COURSE_PERIOD between 1 and 8),
COURSE_NO char(5),
COURSE_SEG char(1),
constraint SS_CNCS_FK foreign key (COURSE_NO,COURSE_SEG) references SIS_COURSE(COURSE_NO,COURSE_SEG));
----------------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 14:04:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
