Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2012
    Posts
    12

    Unanswered: Constraint Problem

    Ok so im running into some issues executing my code.
    So creating the table

    CREATE TABLE Penalty(
    Penalty_Type CHAR(8),
    MinDay NUMERIC(3,0) NOT NULL,
    MaxDay NUMERIC(3,0) NOT NULL,
    "Cost" NUMERIC(3,2) NOT NULL,
    CONSTRAINT Penalty_PenaltyType_pk PRIMARY KEY(Penalty_Type),
    CONSTRAINT Penalty_PenaltyType_ck CHECK(Penalty_Type LIKE '[P][e][n][a][l][t][y][0-9]')
    );

    Then inserting the data

    INSERT ALL
    INTO Insurance VALUES ('N','No Cover',0)
    INTO Insurance VALUES ('P', 'Partial Cover', 20.99)
    INTO Insurance VALUES ('F', 'Full Cover', 35.5)
    SELECT * FROM dual;

    Then getting this error message,

    SQL Error: ORA-02290: check constraint (SYSTEM.INSURANCE_INSURANCEID_CK) violated
    02290. 00000 - "check constraint (%s.%s) violated"
    *Cause: The values being inserted do not satisfy the named check

    *Action: do not insert values that violate the constraint.

    then i remove the constraint and i get this one, i cant seem to win

    SQL Error: ORA-01438: value larger than specified precision allowed for this column
    01438. 00000 - "value larger than specified precision allowed for this column"
    *Cause: When inserting or updating records, a numeric value was entered
    that exceeded the precision defined for the column.
    *Action: Enter a value that complies with the numeric column's precision,
    or use the MODIFY option with the ALTER TABLE command to expand
    the precision.

    any help greatly appreciated

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    consider to not use CHAR; but only use VARCHAR2 datatype
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2012
    Posts
    12

    linked wrong table :S

    The table in question (posted wrong one last time)

    CREATE TABLE Insurance(
    InsuranceID VARCHAR2(1),
    InsuranceType NVARCHAR2(15) NOT NULL,
    Price NUMERIC(5,2),
    CONSTRAINT Insurance_InsuranceID_pk PRIMARY KEY(InsuranceID),
    CONSTRAINT Insurance_InsuranceID_ck CHECK(InsuranceID LIKE '[A-Z]') );

    The insert command

    INSERT ALL
    INTO Insurance VALUES ('N','No Cover',0)
    INTO Insurance VALUES ('P', 'Partial Cover', 20.99)
    INTO Insurance VALUES ('F', 'Full Cover', 35.5)
    SELECT * FROM dual;

    The error
    Error report:
    SQL Error: ORA-02290: check constraint (SYSTEM.INSURANCE_INSURANCEID_CK) violated
    02290. 00000 - "check constraint (%s.%s) violated"
    *Cause: The values being inserted do not satisfy the named check

    *Action: do not insert values that violate the constraint.

    P.S. anacedent thanks for the suggestion, tried it but it didn't work.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it works for me
    Code:
    06:06:52 SQL> 
    CREATE TABLE Insurance( 
    InsuranceID VARCHAR2(1),
    InsuranceType NVARCHAR2(15) NOT NULL,
    Price NUMERIC(5,2),
    CONSTRAINT Insurance_InsuranceID_pk PRIMARY KEY(InsuranceID),
    CONSTRAINT Insurance_InsuranceID_ck CHECK(InsuranceID between 'A' and 'Z') );
    06:06:56 SQL> 06:06:56   2  06:06:56   3  06:06:56   4  06:06:56   5  06:06:56   6  
    Table created.
    
    06:06:57 SQL> 
    INSERT ALL
    INTO Insurance VALUES ('N','No Cover',0) 
    INTO Insurance VALUES ('P', 'Partial Cover', 20.99) 
    INTO Insurance VALUES ('F', 'Full Cover', 35.5) 
    SELECT * FROM dual;
    06:07:12 SQL> 06:07:12   2  06:07:12   3  06:07:12   4  06:07:12   5  
    3 rows created.
    
    06:07:12 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2012
    Posts
    12
    CONSTRAINT Insurance_InsuranceID_ck CHECK(InsuranceID between 'A' and 'Z') ); works for me too thanks

    Any ideas to why CONSTRAINT Insurance_InsuranceID_ck CHECK(InsuranceID LIKE '[A-Z]') didnt work?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    none of the INSERTED rows began with "[" character.
    SQL does not default to use REGEX expressions
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2012
    Posts
    12
    How would i best phrase it if there are multiple characters in the string such as
    CONSTRAINT Sale_Rego_ck CHECK(Rego LIKE '[0-9][0-9][0-9][A-Z][A-Z][A-Z]')

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ithrol View Post
    How would i best phrase it if there are multiple characters in the string such as
    CONSTRAINT Sale_Rego_ck CHECK(Rego LIKE '[0-9][0-9][0-9][A-Z][A-Z][A-Z]')
    Maybe, instead of guessing syntax of condition allowing regular expression, you should search it in the documentation. It is available e.g. online on http://tahiti.oracle.com/
    From 10gR1, Oracle uses REXEXP_LIKE condition for treating regular expression patterns.

    Just curious: shall INSURENCEID really contain three digits followed by three leteters in uppercase without anything among them? Quite impossible for one letter long string.
    What is wrong with anacedent's constraint?

  9. #9
    Join Date
    Sep 2012
    Posts
    12
    Thanks I have looked at http://docs.oracle.com/cd/E11882_01/...112/e26088.pdf
    I still couldn't quite find what i'm looking for.

    The create table is , the red being the constraint in question.
    CREATE TABLE Vehicle(
    Rego CHAR(6),
    "Year" NUMERIC(4,0) NOT NULL,
    CONSTRAINT Vehicle_Rego_ck CHECK(Rego LIKE '[0-9][0-9][0-9][A-Z][A-Z][A-Z]'),
    CONSTRAINT Vehicle_Rego_pk PRIMARY KEY (Rego),
    CONSTRAINT Vehicle_Year_ck CHECK("Year" BETWEEN 1908 AND 2100) );


    My input code is

    INSERT ALL
    INTO Vehicle VALUES ('114KKJ',2007)
    SELECT * FROM dual;


    If i was to make a constraint to check imputed values are [0-9][0-9][0-9][A-Z][A-Z][A-Z], could it be done using a LIKE? If not another method would be?

    Also thanks very much anacedent for your help with the last one, but now i cant quite figure out how to expand it into a string with a longer length.

    Any help greatly appreciated
    Last edited by ithrol; 09-07-12 at 20:00. Reason: Wrong table insert code

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    slow learner?
    unwilling or incapable to Read The Fine Manual yourself on REGEXP_LIKE

    >[0-9][0-9][0-9][A-Z][A-Z][A-Z]
    Must the leftmost all 3 places contain digits?
    Must the rightmost all 3 places contain CAPITAL letters?
    Are any blank/space "CHR(32)" characters allowed anywhere?
    is "1A" valid content?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Sep 2012
    Posts
    12
    >[0-9][0-9][0-9][A-Z][A-Z][A-Z]
    Must the leftmost all 3 places contain digits? yes
    Must the rightmost all 3 places contain CAPITAL letters? yes
    Are any blank/space "CHR(32)" characters allowed anywhere? no
    is "1A" valid content? No needs to be six characters long

    and i have read
    REGEXP_LIKE
    but still couldn't quite understand how to do what i'm trying to do.

    and yes i am rather new to Oracle.

    Thanks for all the help so far

  12. #12
    Join Date
    Sep 2012
    Posts
    12
    Im a fool . . . REGEXP_LIKE is not the same as LIKE, I will play around with it and try and make it work. Sorry for frustrating you

  13. #13
    Join Date
    Sep 2012
    Posts
    12
    Ok so i'm slowly getting there i think, ive now got this code
    CONSTRAINT Vehicle_Rego_ck CHECK( REGEXP_LIKE (Rego, '^(0-9)(0-9)(0-9)(A-Z)(A-Z)(A-Z)$','c')) to create my constraint. however it still isn't working.
    My full code is below, also sorry about before kept linking wrong insert to create table. Thanks

    Create Table

    CREATE TABLE Vehicle(
    Rego CHAR(6),
    Description NVARCHAR2(60) NOT NULL,
    Make NVARCHAR2(15) NOT NULL,
    "Model" NVARCHAR2(15) NOT NULL,
    "Year" NUMERIC(4,0) NOT NULL,
    "Value" NUMERIC(6,0) NOT NULL,
    EngineCapacity NUMERIC(2,1) NOT NULL,
    FuelTank NUMERIC(3,0) NOT NULL,
    Odometer NUMERIC(6,0) NOT NULL,
    PurchaseDate DATE NOT NULL,
    NextUpdate DATE,
    VehicleTypeID NUMERIC(1,0) NOT NULL,
    FuelTypeID NUMERIC(1,0) NOT NULL,
    CONSTRAINT Vehicle_Rego_ck CHECK( REGEXP_LIKE (Rego, '^(0-9)(0-9)(0-9)(A-Z)(A-Z)(A-Z)$','c')),
    CONSTRAINT Vehicle_Rego_pk PRIMARY KEY (Rego),
    CONSTRAINT Vehicle_Year_ck CHECK("Year" BETWEEN 1908 AND 2100) );


    Insert Data

    INSERT ALL
    INTO Vehicle VALUES ('114KKJ','Black, Auto, Central locking, CD, A/C, Cruise','Toyota','Landcruiser',2007,63050,4.2,78,1 0649,'24-Sep-07','1-OCT-09',3,2)
    SELECT * FROM dual;


    Error

    Error starting at line 197 in command:
    INSERT ALL
    INTO Vehicle VALUES ('114KKJ','Black, Auto, Central locking, CD, A/C, Cruise','Toyota','Landcruiser',2007,63050,4.2,78,1 0649,'24-Sep-07','1-OCT-09',3,2)
    SELECT * FROM dual
    Error report:
    SQL Error: ORA-02290: check constraint (SYSTEM.VEHICLE_REGO_CK) violated
    02290. 00000 - "check constraint (%s.%s) violated"
    *Cause: The values being inserted do not satisfy the named check

    *Action: do not insert values that violate the constraint.

  14. #14
    Join Date
    Sep 2012
    Posts
    12
    CONSTRAINT Vehicle_Rego_ck CHECK( REGEXP_LIKE (Rego, '^[0-9][0-9][0-9][A-Z][A-Z][A-Z]$','c'))
    Works, Thanks heaps anacedent this has been baffling me for days

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •