| Author |
 Topic  |
|
|
andretti
New Member
70 Posts |
 Posted - 12 March 2005 : 15:23:40
|
I need to know what I need to do to convert the following sql script to a dbs file that will work with access and snitz. Or is it possible that someone here can convert it for me?
Thanks in advance for your time
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BOOK_SUBCATEGORIES_BOOK_CATEGORIES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[BOOK_SUBCATEGORIES] DROP CONSTRAINT FK_BOOK_SUBCATEGORIES_BOOK_CATEGORIES
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BOOK_BOOK_SUBCATEGORIES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[BOOK] DROP CONSTRAINT FK_BOOK_BOOK_SUBCATEGORIES
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK_CATEGORIES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK_CATEGORIES]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK_RATING]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK_RATING]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK_READING_LIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK_READING_LIST]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK_SUBCATEGORIES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BOOK_SUBCATEGORIES]
GO
CREATE TABLE [dbo].[BOOK] (
[book_id] [int] IDENTITY (1, 1) NOT NULL ,
[title] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[author] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[post_date] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[hit] [int] NULL ,
[member_id] [int] NULL ,
[keyword] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[category] [int] NULL ,
[parent_id] [int] NULL ,
[show] [int] NULL ,
[rating] [int] NOT NULL ,
[votes] [int] NOT NULL ,
[summary] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOOK_CATEGORIES] (
[bcat_id] [int] IDENTITY (1, 1) NOT NULL ,
[cat_name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOOK_RATING] (
[brating_id] [int] IDENTITY (1, 1) NOT NULL ,
[comments] [text] COLLATE Latin1_General_CI_AS NULL ,
[rating] [int] NULL ,
[rate_by] [int] NULL ,
[book] [int] NULL ,
[rate_date] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOOK_READING_LIST] (
[br_id] [int] IDENTITY (1, 1) NOT NULL ,
[book_id] [int] NULL ,
[member_id] [int] NULL ,
[status] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[start_date] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[end_date] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOOK_SUBCATEGORIES] (
[bsubcat_id] [int] IDENTITY (1, 1) NOT NULL ,
[subcat_name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[cat_id] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BOOK] ADD
CONSTRAINT [DF_BOOK_rating] DEFAULT (0) FOR [rating],
CONSTRAINT [DF_BOOK_votes] DEFAULT (0) FOR [votes],
CONSTRAINT [PK_BOOK] PRIMARY KEY CLUSTERED
(
[book_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BOOK_CATEGORIES] ADD
CONSTRAINT [PK_BOOK_CATEGORIES] PRIMARY KEY CLUSTERED
(
[bcat_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BOOK_SUBCATEGORIES] ADD
CONSTRAINT [PK_BOOK_SUBCATEGORIES] PRIMARY KEY CLUSTERED
(
[bsubcat_id]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[BOOK] ADD
CONSTRAINT [FK_BOOK_BOOK_SUBCATEGORIES] FOREIGN KEY
(
[category]
) REFERENCES [dbo].[BOOK_SUBCATEGORIES] (
[bsubcat_id]
)
GO
alter table [dbo].[BOOK] nocheck constraint [FK_BOOK_BOOK_SUBCATEGORIES]
GO
ALTER TABLE [dbo].[BOOK_SUBCATEGORIES] ADD
CONSTRAINT [FK_BOOK_SUBCATEGORIES_BOOK_CATEGORIES] FOREIGN KEY
(
[cat_id]
) REFERENCES [dbo].[BOOK_CATEGORIES] (
[bcat_id]
)
GO
alter table [dbo].[BOOK_SUBCATEGORIES] nocheck constraint [FK_BOOK_SUBCATEGORIES_BOOK_CATEGORIES]
GO
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 13 March 2005 : 05:34:07
|
| the dbs mod won't create any of the foreign key constraints, also, since you have all the queries you need, you could just write a basic asp page to execute them |
 |
|
|
andretti
New Member
70 Posts |
Posted - 13 March 2005 : 06:39:31
|
thanks for the idea on that but writing the asp page is still above my knowledge.
So os there any way to convert the above to work with snitz database as a mod? |
|
|
| |
Topic |
|
|
|
Topic Locked
