checks or constraints?

My table has two columns called offer_date and availability_date

and I want to put a constraint that availability_date should

always be earlier than offer date and if values are inserted into

the table it should return DBMS_server_output:

"offer_date cannot be earlier than availability_date"

Name Null? Type
PROPERTY_ID VARCHAR2(10)
TYPE VARCHAR2(10)
ASKING_PRICE NUMBER(10,2)
SELLING_PRICE NUMBER(10,2)
OFFER_DATE DATE
AVAILABILITY_DATE DATEYou will most likely have to implement using a TRIGGER since you have a specific message you want to display. However, since this is a non-dbms specific area, and not all DBMSs will allow you to implement a trigger, I will not recommend any specific trigger syntax.|||An alternative could be to store the difference of the two instead of the availability_date (possibly with a view on top of this, returning the two dates).
Then you could use a simple check constraint ( >= 0) on that difference.|||I'm 99.9% certain you are using Oracle, right?

You can create a check constraint like this:
alter table x add constraint dates_chk check (offer_date >= availability_date);
However, that will not give you the specific error message you mentioned, it will give you:

ORA-02290: check constraint (MYSCHEMA.DATES_CHK) violated

It is easy to trap that error message in an application, see what constraint was violated, and present a better message. I would not advocate using a trigger instead merely to allow a bespoke message. (Aside: it would be nice if Oracle allowed you to define bespoke error messages for each constraint, wouldn't it!)

I note you said this: "it should return DBMS_server_output [the message]". I presume you mean DBMS_OUTPUT.PUT_LINE? If so, that is wrong: never use DBMS_OUTPUT to handle error messages, it is only suitable for simple debugging etc. If you were writing a trigger you should call raise_application_error like this:
raise_application_error(-20001,'offer_date cannot be earlier than availability_date');

Checking that an object is not empty

Hi,

Does anyone know the correct syntax to check the contents of an object variable. I want to do this as part of a constraint,

TIA

R

Variables of type Object are not support in expressions -

"The data type of variable "User::Variable" is not supported in an expression."

|||

Thanks for your reply.

I have a problem then. I want to pass in this object to use in an ExecuteSQL task, as my input parameter. However, because of some conditional execution, there may not be anything to update. This means that the object has nothing in it, and at the moment. This is causing my task to fail.

Any ideas?

|||

You could read the object variable inside a script task, and check for it equalling Nothing.

Then set a boolean variable indicating whether it is populated or not and put an expression on the precedence constraint that checks the boolean variable to see whether it should execute or not.

-Jamie

Checking if CHECK Constraints exist

Hi,

How do I check if a check constraint on a table already exists or not?

Thank you.

see the Books Online for more information on INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE|||Thank you...

Checking Foreign Keys in script

I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>