Sybase - Proper Sytnax to Alter an existing table and ADD a constraint

I am in the midst of defing sybase tables and I am using the templates from a m-sql table, I bellieve.

I need to translate the following syntax to properly run on sybsae properly:

ALTER TABLE [scehedule_exception] WITH NOCHECK ADD
      CONSTRAINT [DF_schedule_exception_date] DEFAULT (getdate()) FOR [date],
      CONSTRAINT [DF_schedule_exception_event_id] DEFAULT (0) FOR [event_id]

***
I need Sytnax
mahpogAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:
Which Sybase database? They have three main ones and the syntax is different in all of them...

Assuming you mean Sybase ASE, neither of these are actually constraints. The correct syntax is:

ALTER TABLE schedule_exception
REPLACE date DEFAULT getdate()

ALTER TABLE schedule_exception
REPLACE event_id DEFAULT 0

This will not affect any existing rows in the table. Also if event_id has a unique index or primary key on it this will fail the second time the default is used. Finally "date" is a keyword in most recent versions of ASE.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mahpogAuthor Commented:
Sorry I did not get back sooner. I unexpectedly got horrible ill for 4-5 days. I am back now.

I am using Sybase SQL Server release 10.0

Are these constraints only purpose is to define "defaults" If so, then not such a big deal. I was no familiar with constraints.

Here is the complete SQL to buld and define the Tables in question, but not in sybase.

******
CREATE TABLE [dbo].[event] (
      [id] [int] IDENTITY (1, 1) NOT NULL ,
      [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [date_started] [smalldatetime] NOT NULL ,
      [date_ended] [smalldatetime] NULL ,
      [time_started] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [time_ended] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [is_all_day] [tinyint] NOT NULL ,
      [repeat_type] [tinyint] NOT NULL ,
      [color] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [date_updated] [smalldatetime] NOT NULL ,
      [date_created] [smalldatetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[event_exception] (
      [date] [smalldatetime] NOT NULL ,
      [event_id] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[event] WITH NOCHECK ADD
      CONSTRAINT [PK_event] PRIMARY KEY  CLUSTERED
      (
            [id]
      )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[event] WITH NOCHECK ADD
      CONSTRAINT [DF_event_name] DEFAULT ('') FOR [name],
      CONSTRAINT [DF_event_description] DEFAULT ('') FOR [description],
      CONSTRAINT [DF_event_date_started] DEFAULT (getdate()) FOR [date_started],
      CONSTRAINT [DF_event_date_ended] DEFAULT (null) FOR [date_ended],
      CONSTRAINT [DF_event_time_started] DEFAULT ('00:00') FOR [time_started],
      CONSTRAINT [DF_event_time_ended] DEFAULT ('00:00') FOR [time_ended],
      CONSTRAINT [DF_event_is_all_day] DEFAULT (0) FOR [is_all_day],
      CONSTRAINT [DF_event_repeat_type] DEFAULT (0) FOR [repeat_type],
      CONSTRAINT [DF_event_color] DEFAULT ('') FOR [color],
      CONSTRAINT [DF_event_date_updated] DEFAULT (getdate()) FOR [date_updated],
      CONSTRAINT [DF_event_date_created] DEFAULT (getdate()) FOR [date_created]
GO

Thanks for your time.
0
Joe WoodhousePrincipal ConsultantCommented:
Wow, SS 10.0, didn't think there were many of those left around. 8-)

Yes, it looks like here you are only defining defaults, so the syntax as previously provided is all you need for that.

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

mahpogAuthor Commented:
Thanks for your response. So, I am just setting up defaults.  figures. Thank you!
0
mahpogAuthor Commented:
I enjoy the feedback received. I learn from these.
0
Joe WoodhousePrincipal ConsultantCommented:
Well, from the full code you gave us later, you are making "genuine" constraints (ie PK constraints), but for the section in your original post, it's all just defaults.

You ok with the Sybase syntax for everything else? You can find all Sybase manuals online at www.sybase.com/support

You will have to look in the "Archive" section for the 10.0 manuals.

Good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.