USE factory
CREATE TABLE worker
(workerno int NOT NULL PRIMARY KEY,
workername char(8),
workersex char(2)DEFAULT'男',
workerborndate datetime,
partymember bit DEFAULT'否',
timetowork datetime,
departno int
)
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')
消息 2714,级别 16,状态 6,第 2 行
数据库中已存在名为 'worker' 的对象。
为什么啊?
CREATE TABLE worker
(workerno int NOT NULL PRIMARY KEY,
workername char(8),
workersex char(2)DEFAULT'男',
workerborndate datetime,
partymember bit DEFAULT'否',
timetowork datetime,
departno int
)
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')
消息 2714,级别 16,状态 6,第 2 行
数据库中已存在名为 'worker' 的对象。
为什么啊?
GO --如果原来没存在,加上GO试试。
CREATE TABLE worker
(workerno int NOT NULL PRIMARY KEY,
workername char(8),
workersex char(2)DEFAULT'男',
workerborndate datetime,
partymember bit DEFAULT'否',
timetowork datetime,
departno int
)
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')
这个》。
use factory
go
if exists(selct 1 from sysobjects where name='worker')
drop table worker
go
CREATE TABLE worker
(workerno int NOT NULL PRIMARY KEY,
workername char(8),
workersex char(2)DEFAULT'男',
workerborndate datetime,
partymember bit DEFAULT'否',
timetowork datetime,
departno int
)
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')
或者
2、
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')
If object_id('worker') is not null
drop table workerCREATE TABLE worker
(workerno int NOT NULL PRIMARY KEY,
workername char(8),
workersex char(2)DEFAULT'男',
workerborndate datetime,
partymember bit DEFAULT'否',
timetowork datetime,
departno int
)
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')
这个数据类型也有问题,可以改成CHAR(2)
“消息 3726,级别 16,状态 1,第 2 行无法删除对象 'worker',因为该对象正由一个 FOREIGN KEY 约束引用。”
所以我现在该考虑如何卸掉原“worker”了。
各位高手有何指教,不胜感激!
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME='worker')
DROP TABLE worker
GO
CREATE TABLE worker
(workerno int NOT NULL PRIMARY KEY,
workername char(8),
workersex char(2)DEFAULT'男',
workerborndate datetime,
partymember bit DEFAULT'否',
timetowork datetime,
departno int
)
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')CREATE TABLE depart
(departno int NOT NULL PRIMARY KEY,
departname char(10)
)
INSERT INTO depart VALUES('101','财务部')
--INSERT INTO depart VALUES('102','人事部')
--INSERT INTO depart VALUES('103','市场部')
CREATE TABLE salary
(workerno int NOT NULL,
workername char(8),
salarydate datetime NOT NULL PRIMARY KEY,
salary decimal(6,1),
FOREIGN KEY(workerno)REFERENCES worker(workerno)
)
INSERT INTO salary VALUES('1','孙华','01/04/04','1201.5')
INSERT INTO salary VALUES('3','陈明','01/04/04','1350.6')
INSERT INTO salary VALUES('7','程西','01/04/04','750.8')
INSERT INTO salary VALUES('2','孙天奇','01/04/04','900.0')
INSERT INTO salary VALUES('9','刘夫文','01/04/04','2006.8')
INSERT INTO salary VALUES('11','刘欣','01/04/04','1250.0')
INSERT INTO salary VALUES('5','余慧','01/04/04','725.0')
INSERT INTO salary VALUES('8','张旗','01/04/04','728.0')
INSERT INTO salary VALUES('13','王小燕','01/04/04','1200.0')
INSERT INTO salary VALUES('4','李华','01/04/04','1500.5')
INSERT INTO salary VALUES('10','陈涛','01/04/04','1245.8')
INSERT INTO salary VALUES('14','李艺','01/04/04','1000.6')
INSERT INTO salary VALUES('12','李涵','01/04/04','1345.0')
INSERT INTO salary VALUES('15','魏君','01/04/04','1100.0')
INSERT INTO salary VALUES('6','欧阳少兵','01/04/04','11085.0')
INSERT INTO salary VALUES('1','孙华','02/0304','1206.5')
INSERT INTO salary VALUES('3','陈明','02/03/04','1355.6')
INSERT INTO salary VALUES('7','程西','02/03/04','755.8')
INSERT INTO salary VALUES('2','孙天奇','02/03/04','905.0')
INSERT INTO salary VALUES('9','刘夫文','02/03/04','2011.8')
INSERT INTO salary VALUES('11','刘欣','02/03/04','1255.0')
INSERT INTO salary VALUES('5','余慧','02/03/04','730.0')
INSERT INTO salary VALUES('8','张旗','02/03/04','733.0')
INSERT INTO salary VALUES('13','王小燕','02/03/04','1205.0')
INSERT INTO salary VALUES('4','李华','02/03/04','1505.5')
INSERT INTO salary VALUES('10','陈涛','02/03/04','1250.8')
INSERT INTO salary VALUES('14','李艺','02/03/04','1005.6')
INSERT INTO salary VALUES('12','李涵','02/03/04','1350.0')
INSERT INTO salary VALUES('15','魏君','02/03/04','1105.0')
INSERT INTO salary VALUES('6','欧阳少兵','02/03/04','1085.0')
消息 3726,级别 16,状态 1,第 2 行
无法删除对象 'worker',因为该对象正由一个 FOREIGN KEY 约束引用。
消息 102,级别 15,状态 1,第 30 行
')' 附近有语法错误
到底怎们回事啊?请高手运行一下,拉我一把!
GO
IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME='worker')
DROP TABLE worker
GO
CREATE TABLE worker
(workerno int NOT NULL PRIMARY KEY,
workername char(8),
workersex char(2)DEFAULT'男',
workerborndate VARCHAR(20),
partymember VARCHAR(10) DEFAULT'否',
timetowork VARCHAR(20),
departno int
)
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')
GO
IF object_id('depart') IS NOT NULL
DROP TABLE depart
GO
CREATE TABLE depart
(departno int NOT NULL PRIMARY KEY,
departname char(10)
)
INSERT INTO depart VALUES('101','财务部')
--INSERT INTO depart VALUES('102','人事部')
--INSERT INTO depart VALUES('103','市场部')GO
IF object_id('salary') IS NOT NULL
DROP TABLE salary
GO
CREATE TABLE salary
(workerno int NOT NULL,
workername char(8),
salarydate VARCHAR(20) NOT NULL ,
salary decimal(6,1),
FOREIGN KEY(workerno)REFERENCES worker(workerno)
)
INSERT INTO salary VALUES('1','孙华','01/04/04','1201.5')
INSERT INTO salary VALUES('3','陈明','01/04/04','1350.6')
INSERT INTO salary VALUES('7','程西','01/04/04','750.8')
INSERT INTO salary VALUES('2','孙天奇','01/04/04','900.0')
INSERT INTO salary VALUES('9','刘夫文','01/04/04','2006.8')
INSERT INTO salary VALUES('11','刘欣','01/04/04','1250.0')
INSERT INTO salary VALUES('5','余慧','01/04/04','725.0')
INSERT INTO salary VALUES('8','张旗','01/04/04','728.0')
INSERT INTO salary VALUES('13','王小燕','01/04/04','1200.0')
INSERT INTO salary VALUES('4','李华','01/04/04','1500.5')
INSERT INTO salary VALUES('10','陈涛','01/04/04','1245.8')
INSERT INTO salary VALUES('14','李艺','01/04/04','1000.6')
INSERT INTO salary VALUES('12','李涵','01/04/04','1345.0')
INSERT INTO salary VALUES('15','魏君','01/04/04','1100.0')
INSERT INTO salary VALUES('6','欧阳少兵','01/04/04','11085.0')
INSERT INTO salary VALUES('1','孙华','02/0304','1206.5')
INSERT INTO salary VALUES('3','陈明','02/03/04','1355.6')
INSERT INTO salary VALUES('7','程西','02/03/04','755.8')
INSERT INTO salary VALUES('2','孙天奇','02/03/04','905.0')
INSERT INTO salary VALUES('9','刘夫文','02/03/04','2011.8')
INSERT INTO salary VALUES('11','刘欣','02/03/04','1255.0')
INSERT INTO salary VALUES('5','余慧','02/03/04','730.0')
INSERT INTO salary VALUES('8','张旗','02/03/04','733.0')
INSERT INTO salary VALUES('13','王小燕','02/03/04','1205.0')
INSERT INTO salary VALUES('4','李华','02/03/04','1505.5')
INSERT INTO salary VALUES('10','陈涛','02/03/04','1250.8')
INSERT INTO salary VALUES('14','李艺','02/03/04','1005.6')
INSERT INTO salary VALUES('12','李涵','02/03/04','1350.0')
INSERT INTO salary VALUES('15','魏君','02/03/04','1105.0')
INSERT INTO salary VALUES('6','欧阳少兵','02/03/04','1085.0')这样可以运行,太多错误了。你自己对比对比吧
例如
worker表和A表有关联关系,修改A表删除worker表与A表的外键关系,然后再次drop worker表~
--1 删除外键
--2 复制数据备份
--3、删除worker表
--4、重新创建表worker
--5、导入数据
--6、补上外键--或原worker表修改表名worker_pqs,创建worker表,数据导入过来
drop table worker
再次:创建表,如果还是无法创建,则先将外键删除
最后再创建表。
(departno int NOT NULL PRIMARY KEY,
departname char(10)
)
INSERT INTO depart VALUES('101','财务部')
INSERT INTO depart VALUES('102','人事部')
INSERT INTO depart VALUES('103','市场部')
消息 102,级别 15,状态 1,第 30 行
')' 附近有语法错误。
消息 102,级别 15,状态 1,第 31 行
')' 附近有语法错误。
消息 102,级别 15,状态 1,第 32 行
')' 附近有语法错误。这个增加记录这样写不对吗?
是什么意思?
要怎么解决?
创建之前判断该数据库是否存在
--检查数据库中有没有叫databaseName名的数据库
if exists (select * from databases where name='databaseName')
--如果有则删除掉这个数据库
drop database 'databaseName'
go
--批处理语句执行完毕
--重新创建database数据库
Create DATABASE database-name
--指定您要运行的数据库
use database
go
--检查是否有该数据表
if exists (select * from objects where name ='tableName')
--如果有责删除数据表
drop table tableName
go
--重新创建数据表
create table tableName
(
...........
)
go如此便可!希望楼主早日解决问题
USE factory
go
\\查看数据库中是否存在worker表,存在就删了
if exists (select * from sysobjects where name='worker')
drop table worker
go
CREATE TABLE worker
(workerno int NOT NULL PRIMARY KEY,
workername char(8),
workersex char(2)DEFAULT'男',
workerborndate datetime,
partymember bit DEFAULT'否',
timetowork datetime,
departno int
)
go
INSERT INTO worker VALUES('1','孙华','男','01/03/52','是','10/10/70','101')
INSERT INTO worker VALUES('3','陈明','男','05/08/45','否','01/01/65','102')
INSERT INTO worker VALUES('7','程西','女','06/10/80','否','07/10/02','101')
INSERT INTO worker VALUES('2','孙天奇','女','03/10/65','是','03/10/87','102')
INSERT INTO worker VALUES('9','刘夫文','男','01/11/42','否','08/10/60','102')
INSERT INTO worker VALUES('11','刘欣','男','10/08/52','否','01/07/70','101')
INSERT INTO worker VALUES('5','余慧','男','12/04/80','否','07/10/02','103')
INSERT INTO worker VALUES('8','张旗','男','11/10/80','否','07/10/02','102')
INSERT INTO worker VALUES('13','王小燕','女','02/10/64','否','07/15/89','101')
INSERT INTO worker VALUES('4','李华','男','08/07/56','否','07/20/83','103')
INSERT INTO worker VALUES('10','陈涛','男','02/10/58','是','07/12/84','102')
INSERT INTO worker VALUES('14','李艺','女','02/10/63','否','07/20/90','103')
INSERT INTO worker VALUES('12','李涵','男','04/19/65','是','07/10/89','103')
INSERT INTO worker VALUES('15','魏君','男','01/03/52','是','10/10/70','103')
INSERT INTO worker VALUES('6','欧阳少兵','男','12/09/71','是','07/20/92','103')
go
select *
from sysobjects
where id=OBJECT_ID('Worker');