Asad.Safari
سه شنبه 26 آبان 1383, 15:56 عصر
با سلام
من یه دیتابیس جدید با باز میکنم با نام test
و کد زیر توسط Query analizer اجرا میکنم!!!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_courseoffsered_course]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[courseoffsered] DROP CONSTRAINT FK_courseoffsered_course
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_prerequisites_courseoffsered ]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[prerequisites] DROP CONSTRAINT FK_prerequisites_courseoffsered
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_coursetaken_student]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[coursetaken] DROP CONSTRAINT FK_coursetaken_student
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_enrollment_student]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[enrollment] DROP CONSTRAINT FK_enrollment_student
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[course]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[courseoffsered]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[courseoffsered]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[coursetaken]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[coursetaken]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enrollment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[enrollment]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prerequisites]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[prerequisites]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[registereduser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[registereduser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[role]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[userrole]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[userrole]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[course] (
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credit] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[courseoffsered]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[courseoffsered] (
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[seattaken] [int] NOT NULL ,
[capacity] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[coursetaken]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[coursetaken] (
[studentid] [int] NOT NULL ,
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[grade] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[snum] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enrollment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[enrollment] (
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[studentid] [int] NOT NULL ,
[sectionnum] [int] NOT NULL ,
[cnum] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prerequisites]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[prerequisites] (
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prereq] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cnum] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[registereduser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[registereduser] (
[userid] [int] NOT NULL ,
[name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[role] (
[roleid] [int] NOT NULL ,
[role] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[student] (
[studentid] [int] NOT NULL ,
[firstname] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastname] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [int] NULL ,
[major] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[userrole]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[userrole] (
[userid] [int] NOT NULL ,
[roleid] [int] NOT NULL
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[course] WITH NOCHECK ADD
CONSTRAINT [PK_course] PRIMARY KEY CLUSTERED
(
[courseid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[courseoffsered] WITH NOCHECK ADD
CONSTRAINT [PK_courseoffsered] PRIMARY KEY CLUSTERED
(
[courseid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[coursetaken] WITH NOCHECK ADD
CONSTRAINT [PK_coursetaken] PRIMARY KEY CLUSTERED
(
[studentid],
[courseid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[enrollment] WITH NOCHECK ADD
CONSTRAINT [PK_enrollment] PRIMARY KEY CLUSTERED
(
[courseid],
[studentid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prerequisites] WITH NOCHECK ADD
CONSTRAINT [PK_prerequisites] PRIMARY KEY CLUSTERED
(
[courseid],
[prereq]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[registereduser] WITH NOCHECK ADD
CONSTRAINT [PK_registereduser] PRIMARY KEY CLUSTERED
(
[name]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[role] WITH NOCHECK ADD
CONSTRAINT [PK_role] PRIMARY KEY CLUSTERED
(
[roleid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[student] WITH NOCHECK ADD
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[studentid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[courseoffsered] ADD
CONSTRAINT [FK_courseoffsered_course] FOREIGN KEY
(
[courseid]
) REFERENCES [dbo].[course] (
[courseid]
)
GO
ALTER TABLE [dbo].[coursetaken] ADD
CONSTRAINT [FK_coursetaken_student] FOREIGN KEY
(
[studentid]
) REFERENCES [dbo].[student] (
[studentid]
)
GO
ALTER TABLE [dbo].[enrollment] ADD
CONSTRAINT [FK_enrollment_student] FOREIGN KEY
(
[studentid]
) REFERENCES [dbo].[student] (
[studentid]
)
GO
ALTER TABLE [dbo].[prerequisites] ADD
CONSTRAINT [FK_prerequisites_courseoffsered] FOREIGN KEY
(
[courseid]
) REFERENCES [dbo].[courseoffsered] (
[courseid]
)
GO
ولی وقتی به enter price میرم
میبینم هیچ جدولی درست نشده است
مشکل این از چیه؟
با تشکر
من یه دیتابیس جدید با باز میکنم با نام test
و کد زیر توسط Query analizer اجرا میکنم!!!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_courseoffsered_course]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[courseoffsered] DROP CONSTRAINT FK_courseoffsered_course
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_prerequisites_courseoffsered ]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[prerequisites] DROP CONSTRAINT FK_prerequisites_courseoffsered
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_coursetaken_student]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[coursetaken] DROP CONSTRAINT FK_coursetaken_student
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_enrollment_student]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[enrollment] DROP CONSTRAINT FK_enrollment_student
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[course]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[courseoffsered]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[courseoffsered]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[coursetaken]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[coursetaken]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enrollment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[enrollment]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prerequisites]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[prerequisites]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[registereduser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[registereduser]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[role]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[userrole]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[userrole]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[course] (
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credit] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[courseoffsered]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[courseoffsered] (
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[seattaken] [int] NOT NULL ,
[capacity] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[coursetaken]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[coursetaken] (
[studentid] [int] NOT NULL ,
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[grade] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[snum] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[enrollment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[enrollment] (
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[studentid] [int] NOT NULL ,
[sectionnum] [int] NOT NULL ,
[cnum] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[prerequisites]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[prerequisites] (
[courseid] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prereq] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cnum] [int] NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[registereduser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[registereduser] (
[userid] [int] NOT NULL ,
[name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[role] (
[roleid] [int] NOT NULL ,
[role] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[student] (
[studentid] [int] NOT NULL ,
[firstname] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastname] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [int] NULL ,
[major] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[userrole]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[userrole] (
[userid] [int] NOT NULL ,
[roleid] [int] NOT NULL
) ON [PRIMARY]
END
GO
ALTER TABLE [dbo].[course] WITH NOCHECK ADD
CONSTRAINT [PK_course] PRIMARY KEY CLUSTERED
(
[courseid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[courseoffsered] WITH NOCHECK ADD
CONSTRAINT [PK_courseoffsered] PRIMARY KEY CLUSTERED
(
[courseid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[coursetaken] WITH NOCHECK ADD
CONSTRAINT [PK_coursetaken] PRIMARY KEY CLUSTERED
(
[studentid],
[courseid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[enrollment] WITH NOCHECK ADD
CONSTRAINT [PK_enrollment] PRIMARY KEY CLUSTERED
(
[courseid],
[studentid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prerequisites] WITH NOCHECK ADD
CONSTRAINT [PK_prerequisites] PRIMARY KEY CLUSTERED
(
[courseid],
[prereq]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[registereduser] WITH NOCHECK ADD
CONSTRAINT [PK_registereduser] PRIMARY KEY CLUSTERED
(
[name]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[role] WITH NOCHECK ADD
CONSTRAINT [PK_role] PRIMARY KEY CLUSTERED
(
[roleid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[student] WITH NOCHECK ADD
CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
[studentid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[courseoffsered] ADD
CONSTRAINT [FK_courseoffsered_course] FOREIGN KEY
(
[courseid]
) REFERENCES [dbo].[course] (
[courseid]
)
GO
ALTER TABLE [dbo].[coursetaken] ADD
CONSTRAINT [FK_coursetaken_student] FOREIGN KEY
(
[studentid]
) REFERENCES [dbo].[student] (
[studentid]
)
GO
ALTER TABLE [dbo].[enrollment] ADD
CONSTRAINT [FK_enrollment_student] FOREIGN KEY
(
[studentid]
) REFERENCES [dbo].[student] (
[studentid]
)
GO
ALTER TABLE [dbo].[prerequisites] ADD
CONSTRAINT [FK_prerequisites_courseoffsered] FOREIGN KEY
(
[courseid]
) REFERENCES [dbo].[courseoffsered] (
[courseid]
)
GO
ولی وقتی به enter price میرم
میبینم هیچ جدولی درست نشده است
مشکل این از چیه؟
با تشکر