Re: [GENERAL] Constraint Problem

--- Chairudin Sentosa Harjo <chai@prima.net.id> wrote:
> Dear Friends,
>
> I have a very wierd problem. It should be obvious,
> but I can't
> understand why.
>
> I am using Postgresql 6.5.2, SuSe 6.2, Pentium III,
> Kernel Linux 2.2.10.
>
> Please notice the error, it keeps saying
> ck_ngetest_disc_pin,
> ck_ngetest_disc_country
> is the cause of the problem.
> I have inserted the correct value, but why keep
> giving error???
>
> The wierdest part is, when I insert a value to field
> "disc_all", the
> error mentioned
> "ck_ngetest_disc_pin" !!!.... If the constraint was
> violated, the error
> message
> should be "ck_ngetest_disc_all".
>
>
> create table ngetest
>   (
>    custnum              int8 NOT NULL,
>    first_name           varchar(15) NOT NULL,
>    service_type         char NOT NULL,
>    sex                  char NOT NULL,
>    detailed_bill        char NOT NULL,
>    ngetest_status       char NOT NULL,
>    disc_all             char,
>    disc_country         char,
>    disc_pin             char,
>
>    constraint pk_ngetest PRIMARY KEY (custnum),
>
>    constraint ck_ngetest_service_type check
>      (service_type = 'Y' or
>       service_type = 'N' or
>       service_type = 'G' or
>       service_type = 'M' or
>       service_type = 'O' or
>       service_type = 'D'),
>
>    constraint ck_ngetest_status check
>      (ngetest_status = 'A' or
>       ngetest_status = 'I' or
>       ngetest_status = 'S' or
>       ngetest_status = 'T'),
>
>    constraint ck_ngetest_disc_all check
>      (disc_all = 'Y' or
>       disc_all = 'N'),
>    constraint ck_ngetest_disc_country check
>      (disc_country = 'Y' or
>       disc_country = 'N'),
>    constraint ck_ngetest_disc_pin check
>      (disc_pin = 'Y' or
>       disc_pin = 'N')
>   );
>
> insert into ngetest
> (custnum, first_name, service_type, sex,
> detailed_bill, ngetest_status,
> disc_all)
> values
> (1,'Bob','Y','M','Y','A','Y');
> ERROR:  ExecAppend: rejected due to CHECK constraint
> ck_ngetest_disc_pin
>
> insert into ngetest
> (custnum, first_name, service_type, sex,
> detailed_bill, ngetest_status,
> disc_country)
> values
> (2,'Mike','Y','M','Y','A','Y');
> ERROR:  ExecAppend: rejected due to CHECK constraint
> ck_ngetest_disc_pin
>
> insert into ngetest
> (custnum, first_name, service_type, sex,
> detailed_bill, ngetest_status,
> disc_pin)
> values
> (3,'John','Y','M','Y','A','Y');
> ERROR:  ExecAppend: rejected due to CHECK constraint
> ck_ngetest_disc_country
>

In the first and second queries, you omitted a value
in your INSERT statements for the disc_pin field.
Therefore, a NULL value would normally be inserted.
However, this is in violation of your CONSTRAINT
which limits the values to 'Y' or 'N'. In the
third query, your INSERT statement did not provide
a value for disc_country, and this violated your
CONSTRAINT which also limits values to 'Y' or 'N',
since NULL is not amongst them.

Either (a) insert a valid value into each field of
your INSERT statement which contains a CONSTRAINT,
or (b) add a "OR field IS NULL" to the list of valid
values, where field is the apporipriate field name.
Perhaps an implicit value of NULL should be
accepted by the database when a NOT NULL constraint
is not present, but I don't know what the SQL3
standard says. Anways, the above will work.

Hope that helps,

Mike Mascari
(mascarim@yahoo.com)


=====

__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com