asked on

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

ASKER CERTIFIED SOLUTION

Joe Woodhouse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mahpog

ASKER

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.

Joe Woodhouse

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.

mahpog

ASKER

Thanks for your response. So, I am just setting up defaults. figures. Thank you!

mahpog

ASKER

I enjoy the feedback received. I learn from these.

Joe Woodhouse

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!