-
quick question
This is my code for the table Frame
CREATE TABLE Frame(
FrNo NUMBER CONSTRAINT pkFrameNo PRIMARY KEY,
event VARCHAR NOT NULL,
FrMatch VARCHAR NOT NULL,
Duration NUMBER NOT NULL,
Score1 NUMBER(147) NOT NULL CONSTRAINT FrCheckScore CHECK ((Score1 > 0) & (Score1 < 300)),
Score2 NUMBER(147) NOT NULL CONSTRAINT FrCheckScore CHECK ((Score2 > 0) & (Score2 < 300)),
Report char(1000),
);
says it missing left parenthisis, cant see why
but the Report field also needs to have the words OK or INCIDENT. How would i code this?
Thanks p
Paul
-
Several things:
- Oracle recommends use VARCHAR2 not VARCHAR
- Max size number is NUMBER(38), you might mean NUMBER(38,109)? - obviously not if max val is 300 so NUMBER(3) would do.
- You have two constraints with the same name "FrCheckScore"
- I'm pretty sure you can't use "&" for "AND" in a condition (might be wrong on that)
- I'd recommend VARCHAR2 instead of CHAR
Report: Like '%OK%' or Like '%INCIDENT%' ? ? ? ?
Last edited by DaPi; 02-16-2005 at 10:06 AM.
-
Are you storing results of snooker matches?
-
Originally posted by slimdave
Are you storing results of snooker matches?
300 is a lot of "4 away" and "score > 0" wouldn't allow for my standard of play!
I'm wondering if it's 10-pin and the conditions should be >= <=.
-
I missed one:
- you MUST specify a length for a VARCHAR or VARCHAR2
-
For starters, was this coded for an oracle database?
(i)We use data type varchar2() and not just varchar when defining the data type for a column.
(ii) The data type NUMBER has a range from 1-38, so your number(147) will not work here.
(iii) your check constraint (Score1 < 300) .
Hence,
Code:
CREATE TABLE Frame(
FrNo NUMBER(38) CONSTRAINT pkFrameNo PRIMARY KEY,
event VARCHAR2(100) NOT NULL,
FrMatch VARCHAR2(100) NOT NULL,
Duration NUMBER(38) NOT NULL,
Score1 NUMBER(38) NOT NULL CONSTRAINT FrCheckScore1 CHECK (Score1 < 300),
Score2 NUMBER(38) NOT NULL CONSTRAINT FrCheckScore2 CHECK (Score2 < 300),
Report varchar2(1000) NOT NULL CONSTRAINT FrCheckreport CHECK (report in ('OK','INCIDENT'))
);
-
Originally posted by DaPi
300 is a lot of "4 away" and "score > 0" wouldn't allow for my standard of play!
I'm wondering if it's 10-pin and the conditions should be >= <=.
Good catch. For my own standard of bowling, I'd sure like to see that constraint changed to "...>=0".
-
Follow Up - Quick Question
I have modified the code, however still has the Error: ORA-00907: missing right parenthesis.
CREATE TABLE Frame(
FrNo NUMBER(3) CONSTRAINT pkFrameNo PRIMARY KEY,
event VARCHAR2(3) NOT NULL,
FrMatch VARCHAR2(3) CONSTRAINT FRcheckFrame CHECK ((FrNo >= 3) & (FrNo <= 35)),
Duration NUMBER(3) NOT NULL,
Score1 NUMBER(300) NOT NULL CONSTRAINT FrCheckScore1 CHECK ((Score1 > 0) AND (Score1 < 300)),
Score2 NUMBER(300) NOT NULL CONSTRAINT FrCheckScore2 CHECK ((Score2 > 0) AND (Score2 < 300)),
Report VARCHAR2(1000) CONSTRAINT CHECK ((INSTR(Report, 'OK') > 0)OR((INSTR(Report, 'INCIDENT') > 0))),
);
I am storing information for a snooker game, It is one of the projects i am working on. Could anyone give me some more help?
Paul
-
Do I get free tickets to the World Championships if I sort it out?
-
Whats going on with the comma at the end:
Code:
Report VARCHAR2(1000) CONSTRAINT CHECK ((INSTR(Report, 'OK') > 0)OR((INSTR(Report, 'INCIDENT') > 0))),
);
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|