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?
 
Joe WoodhouseConnect With a Mentor Principal 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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
All Courses

From novice to tech pro — start learning today.