How do I delete all tables and foreign key restrai

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I delete all tables and foreign key restrai

Author

Topic

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-11 : 05:15:09

How do I delete all tables and foreign key restraints?

I know how to use the drop command and all that, but I am having an issue I can't figure out.

I made a database with 7 tables and everything worked fine, but I found a better way to make my 7 tables so I made a new query and created my tables.

Well I am getting

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__major__dname__563FA78C". The conflict occurred in database "master", table "dbo.dept", column 'dname'.

And Truncated errors.

How can I just delete everything off my SQL Server Management so I don't have these foreign key issues from other databases or tables?

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-11 : 05:21:02

DROP DATABASE databasename

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-11 : 05:41:33

Alright. However it will not let me drop master as it is a system database.

Here is the exact error.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__major__dname__45D43599". The conflict occurred in database "master", table "dbo.dept", column 'dname'.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-11 : 06:13:18

Reason is:

May be you are trying to insert 'department name' that is not in parent table of dept...

--
Chandu

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-11 : 06:38:40

I see...

My table looks like...

Student(sid,sname,sex,age,year,qpa)
Dept(dname,numphds)
Prof (pname,dname)
Course (cno,cname,dname)
Major(dname,sid)
Section(dname,cno,sectno,pname)
Enroll(sid,grade,dname,cno,sectno)

My Query runs successful when I paste my inserts from Student-Course, but my inserts on the Major table are somehow conflicting with it.

I just can't figure out why. My foreign key reference looks fine in the Major table that references the primary key in the Dept table.

And my inserts look fine as well. hmmm

I think I figured it out. one of the Inserts had a 0 in it and it didn't like that very much when using Integer.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-11 : 06:51:15

quote:
Originally posted by Prosercunus

Alright. However it will not let me drop master as it is a system database.

Here is the exact error.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__major__dname__45D43599". The conflict occurred in database "master", table "dbo.dept", column 'dname'.

Wait.. you created your tables in MASTER?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-11 : 06:52:36

Yeah. I think I did when I first started doing all of this stuff.

Unsure how to remedy this.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-11 : 08:47:54

quote:
Originally posted by Prosercunus

I see...

My table looks like...

Student(sid,sname,sex,age,year,qpa)
Dept(dname,numphds)
Prof (pname,dname)
Course (cno,cname,dname)
Major(dname,sid)
Section(dname,cno,sectno,pname)
Enroll(sid,grade,dname,cno,sectno)

My Query runs successful when I paste my inserts from Student-Course, but my inserts on the Major table are somehow conflicting with it.

I just can't figure out why. My foreign key reference looks fine in the Major table that references the primary key in the Dept table.

And my inserts look fine as well. hmmm

I think I figured it out. one of the Inserts had a 0 in it and it didn't like that very much when using Integer.

Show us the data of dept table and also your INSERT statement of Major table

--
Chandu

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-11 : 08:57:52

I am only having one problem now. I figured out my foreign key constraint problem. 0 as sid wasn't valid and I simply got rid of it since it didn't exist.

However I am having one more problem in the form of...

"String or binary data would be truncated." in my very last table called Enroll. It only happens from my Insert data.

Here is all of my tables and inserts.

The query runs perfectly until I paste my Enroll inserts and try to execute. But the Enroll table and beforehand works fine.

CREATE TABLE student ( sid INTEGER,
sname VARCHAR(40),
sex CHAR(1),
age INTEGER,
year VARCHAR(4),
gpa REAL,
PRIMARY KEY (sid) );

Insert into Student(sid,sname,sex,age,year,gpa)
values('1', 'Jacobs, T.', 'm', '29', '5', '3.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('2', 'Pierson, E.', 'm', '32', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('3', 'Zeene, Ben N.', 'm', '21', '5', '3.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('4', 'Sulfate, Barry M.', 'm', '19', '2', '2.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('5', 'Form, Clara O.', 'f', '18', '1', '3.30')
Insert into Student(sid,sname,sex,age,year,gpa)
values('6', 'Scott, Kim J.', 'm', '20', '1', '3.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('7', 'Sather, Roberto B.', 'm', '22', '4', '2.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('8', 'Stanley, Leotha T.', 'm', '21', '3', '3.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('9', 'Smith, Joyce A.', 'f', '21', '4', '2.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('10', 'Jones, David S.', 'm', '19', '2', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('11', 'Paul, Mary W.', 'f', '23', '5', '3.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('12', 'Soong, V.', 'f', '24', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('13', 'Kellerman, S.', 'f', '21', '3', '2.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('14', 'Cheong, R.', 'm', '24', '5', '3.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('15', 'Borchart, Sandra L.', 'f', '26', '5', '3.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('16', 'Alsberg, David J.', 'm', '25', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('17', 'Thorton, James Q.', 'm', '28', '4', '2.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('18', 'Gooch', 'm', '26', '1', '1.40')
Insert into Student(sid,sname,sex,age,year,gpa)
values('19', 'Smith, L.', 'm', '43', '4', '0.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('20', 'Korpel, E.', 'f', '19', '3', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('21', 'Surk, K.', 'm', '23', '2', '2.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('22', 'Emile, R.', 'm', '18', '1', '2.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('23', 'Bomber, C', 'f', '22', '4', '3.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('24', 'Carter, Jimmy', 'm', '56', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('25', 'Kissinger, Henry', 'm', '58', '5', '3.40')
Insert into Student(sid,sname,sex,age,year,gpa)
values('26', 'Ford, Gerald', 'm', '60', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('27', 'Anderson, P.', 'f', '18', '1', '3.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('28', 'Austin, G.', 'm', '25', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('29', 'Hamilton, S.', 'm', '21', '3', '2.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('30', 'Baker, C.', 'f', '18', '1', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('31', 'Andrews, R.', 'm', '19', '2', '2.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('32', 'Liu, Huihusan', 'm', '29', '5', '3.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('33', 'Chao, Tsechih', 'f', '23', '5', '3.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('34', 'Kasten, Norman L.', 'm', '23', '2', '2.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('35', 'Mathews, John W.', 'm', '29', '5', '3.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('36', 'Burroughs, Susan S.', 'f', '18', '1', '3.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('37', 'Dunbar, D.', 'm', '30', '5', '3.40')
Insert into Student(sid,sname,sex,age,year,gpa)
values('38', 'Auen, B.', 'm', '21', '3', '2.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('39', 'Shoemaker, A.', 'f', '22', '4', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('40', 'Rosemeyer, S', 'f', '21', '3','2.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('41', 'Fisher, C.', 'f', '22', '4', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('42', 'Trude, T.', 'm', '19', '2', '2.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('43', 'Ksar, J.', 'm', '20', '3', '3.40')
Insert into Student(sid,sname,sex,age,year,gpa)
values('44', 'Moeri, S.', 'f', '20', '4', '3.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('45', 'June, Granson', 'f', '18', '1', '3.10')
Insert into Student(sid,sname,sex,age,year,gpa)
values('46', 'Micheal, Zadicki T.', 'm', '22', '2', '2.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('47', 'Roger, Blotter N.', 'm', '21', '3', '1.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('48', 'Natividad, A.', 'f', '25', '5', '4.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('49', 'Villa-lobos, M.', 'm', '47', '5', '3.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('50', 'Moomchi, B.', 'm', '32', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('51', 'Jetplane, Leaving O.', 'm', '30', '1', '0.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('52', 'Fy, Clara I.', 'f', '18', '2', '2.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('53', 'Atny, Mary H.', 'f', '26', '5', '3.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('54', 'Maximillian', 'm', '98', '5', '3.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('55', 'Glitch, R.', 'm', '19', '1', '2.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('56', 'Starry, J.', 'f', '22', '4', '3.30')
Insert into Student(sid,sname,sex,age,year,gpa)
values('57', 'Hiemerschmitz, A.', 'f', '19', '1', '2.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('58', 'Marshmallton', 'm', '20', '3', '3.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('59', 'Ziebart, F.', 'm', '22', '4', '1.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('60', 'Calcmity, J.', 'f', '23', '3', '2.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('61', 'Kennedy, Ed', 'm', '55', '3', '2.30')
Insert into Student(sid,sname,sex,age,year,gpa)
values('62', 'Moses, A.', 'm', '18', '1', '3.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('63', 'Kool, Joseph F.', 'm', '35', '5', '3.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('64', 'Fred, Edwin B.', 'm', '80', '5', '4.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('65', 'Ripper, Jack T.', 'm', '18', '1', '0.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('66', 'Altenhaus, Stuart', 'm', '21', '4', '2.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('67', 'Altenhaus, Gloria', 'f', '22', '5', '4.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('68', 'Crist, J.', 'm', '23', '4', '3.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('69', 'Heilskov, G.', 'm', '23', '4,', '2.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('70', 'Caucutt, B.', 'm', '24', '5', '3.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('71', 'Mark, B.', 'm', '24', '4', '3.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('72', 'Barnes, J.', 'm', '45', '5', '2.30')
Insert into Student(sid,sname,sex,age,year,gpa)
values('73', 'Quarnty, G.', 'f', '20', '2', '4.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('74', 'Andrus, J.', 'f', '21', '3', '3.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('75', 'Jones, A.', 'f', '23', '5', '3.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('76', 'Zorhoff, C.', 'm', '20', '3', '3.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('77', 'Paull, Thomas H.', 'm', '23', '4', '2.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('78', 'Cool, J.', 'm', '18', '2', '2.60')
Insert into Student(sid,sname,sex,age,year,gpa)
values('79', 'Evert, Chris', 'f', '21', '4', '3.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('80', 'Connors, Jimmy', 'm', '25', '1', '0.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('81', 'Smith, Ike Z.', 'm', '33', '1', '1.10')
Insert into Student(sid,sname,sex,age,year,gpa)
values('82', 'News, Nightly', 'm', '15', '1', '1.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('83', 'Jones, Ivan L.', 'm', '55', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('84', 'Smith, R.', 'm', '19', '3', '2.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('85', 'Mayer, N.', 'f', '21', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('86', 'Gonring, J.', 'm', '21', '4', '3.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('87', 'Mueller, D.', 'm', '20', '3', '3.30')
Insert into Student(sid,sname,sex,age,year,gpa)
values('88', 'Bates, M.', 'm', '22', '4', '3.30')
Insert into Student(sid,sname,sex,age,year,gpa)
values('89', 'Longlastname, A.', 'f', '99', '5', '4.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('90', 'Zappa, F.', 'm', '16', '4', '4.00')
Insert into Student(sid,sname,sex,age,year,gpa)
values('91', 'Ghandi, I.', 'f', '78', '1', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('92', 'Kirk, J.', 'm', '34', '5', '2.20')
Insert into Student(sid,sname,sex,age,year,gpa)
values('93', 'Andermanthenol, K.', 'm', '26', '5', '3.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('94', 'Uoiea, Z.', 'f', '19', '2', '3.30')
Insert into Student(sid,sname,sex,age,year,gpa)
values('95', 'Grzlbltz, Q.', 'm', '43', '5', '2.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('96', 'Birch, M.', 'f', '23', '5', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('97', 'Morgan, D.', 'm', '18', '1', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('98', 'Taylor, R.', 'm', '20', '3', '2.80')
Insert into Student(sid,sname,sex,age,year,gpa)
values('99', 'Jones, J.', 'f', '21', '1', '1.10')
Insert into Student(sid,sname,sex,age,year,gpa)
values('100', 'Gringo, C.', 'm', '25', '5', '2.90')
Insert into Student(sid,sname,sex,age,year,gpa)
values('101', 'Davis, Scott P.', 'm', '19', '3', '3.70')
Insert into Student(sid,sname,sex,age,year,gpa)
values('102', 'Bates, Michael L.', 'm', '22', '4', '3.40')
Insert into Student(sid,sname,sex,age,year,gpa)
values('103', 'Kaisler, Janet M.', 'f', '21', '4', '3.50')
Insert into Student(sid,sname,sex,age,year,gpa)
values('104', 'Baskett, Wayse T.', 'm', '23', '3', '2.10')

CREATE TABLE dept ( dname VARCHAR(40),
numphds INTEGER,
PRIMARY KEY (dname) );

Insert into Dept(dname, numphds)
values('Chemical Engineering', '32')
Insert into Dept(dname, numphds)
values('Civil Engineering', '88')
Insert into Dept(dname, numphds)
values('Computer Sciences', '47')
Insert into Dept(dname, numphds)
values('Industrial Engineering', '41')
Insert into Dept(dname, numphds)
values('Mathematics', '129')
Insert into Dept(dname, numphds)
values('Sanitary Engineering', '3')

CREATE TABLE prof ( pname VARCHAR(40),
dname VARCHAR(40),
PRIMARY KEY (pname),
FOREIGN KEY (dname) REFERENCES dept );

Insert into Prof(pname, dname)
values('Brian, C.', 'Computer Sciences')
Insert into Prof(pname, dname)
values('Brown, S.', 'Civil Engineering')
Insert into Prof(pname, dname)
values('Bucket, T.', 'Sanitary Engineering')
Insert into Prof(pname, dname)
values('Clark, E.', 'Civil Engineering')
Insert into Prof(pname, dname)
values('Edison, L.', 'Chemical Engineering')
Insert into Prof(pname, dname)
values('Jones, J.', 'Computer Sciences')
Insert into Prof(pname, dname)
values('Randolph, B.', 'Civil Engineering')
Insert into Prof(pname, dname)
values('Robinson, T.', 'Mathematics')
Insert into Prof(pname, dname)
values('Smith, S.', 'Industrial Engineering')
Insert into Prof(pname, dname)
values('Walter, A.', 'Industrial Engineering')

CREATE TABLE course ( cno INTEGER,
cname VARCHAR(40),
dname VARCHAR(40),
PRIMARY KEY (cno, dname),
FOREIGN KEY (dname) REFERENCES dept );

Insert into Course(cno, cname, dname)
values('302', 'Intro to Programming', 'Computer Sciences')
Insert into Course(cno, cname, dname)
values('310', 'Thermodynamics', 'Chemical Engineering')
Insert into Course(cno, cname, dname)
values('310', 'Intro to Garbage', 'Sanitary Engineering')
Insert into Course(cno, cname, dname)
values('365', 'City Planning', 'Civil Engineering')
Insert into Course(cno, cname, dname)
values('375', 'Highway Engineering', 'Civil Engineering')
Insert into Course(cno, cname, dname)
values('461', 'College Geometry 1', 'Mathematics')
Insert into Course(cno, cname, dname)
values('462', 'College Geometry 2', 'Mathematics')
Insert into Course(cno, cname, dname)
values('467', 'Intro to Data Structures', 'Computer Sciences')
Insert into Course(cno, cname, dname)
values('514', 'Manpower Utilization', 'Industrial Engineering')
Insert into Course(cno, cname, dname)
values('561', 'Advanced City Planning', 'Civil Engineering')
Insert into Course(cno, cname, dname)
values('561', 'Advanced Garbage Collection', 'Sanitary Engineering')
Insert into Course(cno, cname, dname)
values('701', 'Compiler Construction', 'Computer Sciences')
Insert into Course(cno, cname, dname)
values('726', 'Nonlinear Programming', 'Computer Sciences')

CREATE TABLE major ( dname VARCHAR(40),
sid INTEGER,
PRIMARY KEY (dname, sid),
FOREIGN KEY (dname) REFERENCES dept,
FOREIGN KEY (sid) REFERENCES student );

Insert into Major(dname, sid)
values('Chemical Engineering', '25')
Insert into Major(dname, sid)
values('Chemical Engineering', '26')
Insert into Major(dname, sid)
values('Chemical Engineering', '27')
Insert into Major(dname, sid)
values('Chemical Engineering', '28')
Insert into Major(dname, sid)
values('Chemical Engineering', '29')
Insert into Major(dname, sid)
values('Chemical Engineering', '30')
Insert into Major(dname, sid)
values('Chemical Engineering', '31')
Insert into Major(dname, sid)
values('Chemical Engineering', '32')
Insert into Major(dname, sid)
values('Chemical Engineering', '33')
Insert into Major(dname, sid)
values('Chemical Engineering', '34')
Insert into Major(dname, sid)
values('Chemical Engineering', '35')
Insert into Major(dname, sid)
values('Civil Engineering', '36')
Insert into Major(dname, sid)
values('Civil Engineering', '37')
Insert into Major(dname, sid)
values('Civil Engineering', '38')
Insert into Major(dname, sid)
values('Civil Engineering', '39')
Insert into Major(dname, sid)
values('Civil Engineering', '40')
Insert into Major(dname, sid)
values('Civil Engineering', '41')
Insert into Major(dname, sid)
values('Civil Engineering', '42')
Insert into Major(dname, sid)
values('Civil Engineering', '43')
Insert into Major(dname, sid)
values('Civil Engineering', '44')
Insert into Major(dname, sid)
values('Civil Engineering', '45')
Insert into Major(dname, sid)
values('Civil Engineering', '46')
Insert into Major(dname, sid)
values('Civil Engineering', '47')
Insert into Major(dname, sid)
values('Civil Engineering', '48')
Insert into Major(dname, sid)
values('Civil Engineering', '49')
Insert into Major(dname, sid)
values('Civil Engineering', '50')
Insert into Major(dname, sid)
values('Civil Engineering', '51')
Insert into Major(dname, sid)
values('Civil Engineering', '52')
Insert into Major(dname, sid)
values('Civil Engineering', '53')
Insert into Major(dname, sid)
values('Civil Engineering', '54')
Insert into Major(dname, sid)
values('Civil Engineering', '55')
Insert into Major(dname, sid)
values('Civil Engineering', '56')
Insert into Major(dname, sid)
values('Civil Engineering', '57')
Insert into Major(dname, sid)
values('Civil Engineering', '58')
Insert into Major(dname, sid)
values('Civil Engineering', '59')
Insert into Major(dname, sid)
values('Civil Engineering', '60')
Insert into Major(dname, sid)
values('Civil Engineering', '61')
Insert into Major(dname, sid)
values('Civil Engineering', '62')
Insert into Major(dname, sid)
values('Civil Engineering', '63')
Insert into Major(dname, sid)
values('Computer Sciences', '1')
Insert into Major(dname, sid)
values('Computer Sciences', '2')
Insert into Major(dname, sid)
values('Computer Sciences', '3')
Insert into Major(dname, sid)
values('Computer Sciences', '4')
Insert into Major(dname, sid)
values('Computer Sciences', '5')
Insert into Major(dname, sid)
values('Computer Sciences', '6')
Insert into Major(dname, sid)
values('Computer Sciences', '7')
Insert into Major(dname, sid)
values('Computer Sciences', '8')
Insert into Major(dname, sid)
values('Computer Sciences', '9')
Insert into Major(dname, sid)
values('Computer Sciences', '10')
Insert into Major(dname, sid)
values('Computer Sciences', '11')
Insert into Major(dname, sid)
values('Computer Sciences', '12')
Insert into Major(dname, sid)
values('Computer Sciences', '13')
Insert into Major(dname, sid)
values('Computer Sciences', '14')
Insert into Major(dname, sid)
values('Computer Sciences', '15')
Insert into Major(dname, sid)
values('Computer Sciences', '16')
Insert into Major(dname, sid)
values('Computer Sciences', '17')
Insert into Major(dname, sid)
values('Computer Sciences', '18')
Insert into Major(dname, sid)
values('Computer Sciences', '19')
Insert into Major(dname, sid)
values('Computer Sciences', '20')
Insert into Major(dname, sid)
values('Computer Sciences', '21')
Insert into Major(dname, sid)
values('Computer Sciences', '22')
Insert into Major(dname, sid)
values('Computer Sciences', '23')
Insert into Major(dname, sid)
values('Computer Sciences', '24')
Insert into Major(dname, sid)
values('Industrial Engineering', '64')
Insert into Major(dname, sid)
values('Industrial Engineering', '65')
Insert into Major(dname, sid)
values('Industrial Engineering', '66')
Insert into Major(dname, sid)
values('Industrial Engineering', '67')
Insert into Major(dname, sid)
values('Industrial Engineering', '68')
Insert into Major(dname, sid)
values('Industrial Engineering', '69')
Insert into Major(dname, sid)
values('Industrial Engineering', '70')
Insert into Major(dname, sid)
values('Industrial Engineering', '71')
Insert into Major(dname, sid)
values('Industrial Engineering', '72')
Insert into Major(dname, sid)
values('Industrial Engineering', '73')
Insert into Major(dname, sid)
values('Industrial Engineering', '74')
Insert into Major(dname, sid)
values('Industrial Engineering', '75')
Insert into Major(dname, sid)
values('Industrial Engineering', '76')
Insert into Major(dname, sid)
values('Industrial Engineering', '77')
Insert into Major(dname, sid)
values('Industrial Engineering', '78')
Insert into Major(dname, sid)
values('Industrial Engineering', '79')
Insert into Major(dname, sid)
values('Industrial Engineering', '80')
Insert into Major(dname, sid)
values('Industrial Engineering', '81')
Insert into Major(dname, sid)
values('Industrial Engineering', '82')
Insert into Major(dname, sid)
values('Industrial Engineering', '83')
Insert into Major(dname, sid)
values('Mathematics', '1')
Insert into Major(dname, sid)
values('Mathematics', '2')
Insert into Major(dname, sid)
values('Mathematics', '3')
Insert into Major(dname, sid)
values('Mathematics', '84')
Insert into Major(dname, sid)
values('Mathematics', '85')
Insert into Major(dname, sid)
values('Mathematics', '86')
Insert into Major(dname, sid)
values('Mathematics', '87')
Insert into Major(dname, sid)
values('Mathematics', '88')
Insert into Major(dname, sid)
values('Mathematics', '89')
Insert into Major(dname, sid)
values('Mathematics', '90')
Insert into Major(dname, sid)
values('Mathematics', '91')
Insert into Major(dname, sid)
values('Mathematics', '92')
Insert into Major(dname, sid)
values('Mathematics', '93')
Insert into Major(dname, sid)
values('Mathematics', '94')
Insert into Major(dname, sid)
values('Mathematics', '95')
Insert into Major(dname, sid)
values('Mathematics', '96')
Insert into Major(dname, sid)
values('Mathematics', '97')
Insert into Major(dname, sid)
values('Mathematics', '98')
Insert into Major(dname, sid)
values('Mathematics', '99')
Insert into Major(dname, sid)
values('Sanitary Engineering', '4')

CREATE TABLE section ( dname VARCHAR(40),
cno INTEGER,
sectno INTEGER,
pname VARCHAR(40),
PRIMARY KEY (dname, cno, sectno),
FOREIGN KEY (dname) REFERENCES dept,
FOREIGN KEY (cno, dname) REFERENCES course,
FOREIGN KEY (pname) REFERENCES prof );

Insert into Section(dname, cno, sectno, pname)
values('Chemical Engineering', '310', '1', 'Edison, L.')
Insert into Section(dname, cno, sectno, pname)
values('Civil Engineering', '365', '1', 'Randolph, B.')
Insert into Section(dname, cno, sectno, pname)
values('Civil Engineering', '375', '1', 'Brown, S.')
Insert into Section(dname, cno, sectno, pname)
values('Civil Engineering', '561', '1', 'Randolph, B.')
Insert into Section(dname, cno, sectno, pname)
values('Computer Sciences', '302', '1', 'Jones, J.')
Insert into Section(dname, cno, sectno, pname)
values('Computer Sciences', '302', '2', 'Smith, S.')
Insert into Section(dname, cno, sectno, pname)
values('Computer Sciences', '467', '1', 'Jones, J.')
Insert into Section(dname, cno, sectno, pname)
values('Computer Sciences', '701', '1', 'Clark, E.')
Insert into Section(dname, cno, sectno, pname)
values('Computer Sciences', '726', '1', 'Brian, C.')
Insert into Section(dname, cno, sectno, pname)
values('Industrial Engineering', '514', '1', 'Walter, A.')
Insert into Section(dname, cno, sectno, pname)
values('Mathematics', '461', '1', 'Robinson, T.')
Insert into Section(dname, cno, sectno, pname)
values('Mathematics', '462', '1', 'Robinson, T.')
Insert into Section(dname, cno, sectno, pname)
values('Sanitary Engineering', '561', '1', 'Bucket, T.')

CREATE TABLE enroll ( sid INTEGER,
grade CHAR(1),
dname VARCHAR(40),
cno INTEGER,
sectno INTEGER,
PRIMARY KEY (sid, dname, cno, sectno),
FOREIGN KEY (sid) REFERENCES student,
FOREIGN KEY (dname) REFERENCES dept,
FOREIGN KEY (cno, dname) REFERENCES course,
FOREIGN KEY (dname, cno, sectno) REFERENCES section );

Insert into Enroll(sid, grade, dname, cno, sectno)
values('1', '3.00', 'Chemical Engineering', '310', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('2', '3.00', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('3', '3.50', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('4', '4.00', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('5', '3.00', 'Industrial Engineering', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('6', '3.50', 'Computer Sciences', '302', '2')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('7', '4.00', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('8', '4.00', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('9', '3.00', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('10', '2.00', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('11', '3.00', 'Computer Sciences', '302', '2')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('12', '2.50', 'Computer Sciences', '302', '2')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('13', '2.50', 'Computer Sciences', '302', '2')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('14', '2.50', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('15', '3.00', 'Chemical Engineering', '310', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('16', '3.00', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('16', '3.00', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('17', '3.00', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('18', '2.00', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('18', '3.50', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('19', '3.50', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('20', '2.50', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('21', '3.00', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('21', '3,50', 'Industrial Engineering', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('22', '3.00', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('22', '3.00', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('23', '4.00', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('24', '4.00', 'Computer Sciences', '302', '2')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('25', '3.50', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('26', '3.50', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('27', '2.50', 'Industrial Engineering', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('28', '3.00', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('29', '3.00', 'Civil Engineering', '365', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('29', '3.00', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('29', '3.00', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('30', '4.00', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('31', '3.50', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('32', '2.00', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('33', '2.00', 'Civil Engineering', '365', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('33', '3.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('34', '1.00', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('35', '3.00', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('36', '3.00', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('37', '3.00', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('38', '3.00', 'Computer Sciences', '302', '2')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('39', '3.00', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('40', '3.50', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('41', '3.00', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('42', '3.50', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('43', '2.50', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('44', '4.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('45', '3.00', 'Chemical Engineering', '310', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('46', '4.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('47', '1.00', 'Civil Engineering', '365', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('47', '0.00', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('48', '1.50', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('49', '3.50', 'Chemical Engineering', '310', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('49', '3.50', 'Industrial Engineering', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('50', '4.00', 'Industrial Engineering', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('51', '3.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('52', '1.00', 'Computer Sciences', '302', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('52', '4.00', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('53', '4.00', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('54', '3.00', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('55', '2.50', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('56', '2.50', 'Chemical Engineering', '310', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('57', '3.50', 'Computer Science', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('58', '2.00', 'Industrial Technology', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('59', '3.50', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('60', '2.50', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('61', '3.50', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('61', '2.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('62', '2.50', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('63', '2.50', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('64', '4.00', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('64', '3.50', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('66', '1.50', 'Civil Engineering', '365', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('66', '4.00', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('67', '3.50', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('67', '4.00', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('68', '3.50', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('69', '3.50', 'Computer Sciences', '302', '2')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('69', '2.00', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('70', '3.50', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('71', '2.50', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('72', '3.50', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('73', '3.00', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('74', '250', 'Civil Engineering', '365', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('75', '3.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('76', '3.50', 'Civil Engineering', '365', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('77', '2.00', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('78', '3.00', 'Industrial Engineering', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('79', '4.00', 'Civil Engineering', '365', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('80', '4.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('81', '2.50', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('82', '3.50', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('83', '3.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('84', '3.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('85', '3.50', 'Civil Engineering', '375', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('86', '2.50', 'Computer Sciences', '467', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('86', '3.00', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('87', '4.00', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('88', '2.00', 'Chemical Engineering', '310', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('89', '3.50', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values ('90', '3.50', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('90', '3.50', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('91', '4.00', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('92', '4.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('93', '4.00', 'Chemical Engineering', '310', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('94', '2.00', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('96', '4.00', 'Civil Engineering', '365', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('96', '3.00', 'Civil Engineering', '561', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('97', '3.00', 'Industrial Technology', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('98', '4.00', 'Industrial Engineering', '514', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('99', '3.50', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('100', '2.00', 'Computer Sciences', '726', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('101', '4.00', 'Mathematics', '462', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('102', '3.50', 'Mathematics', '461', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('103', '4.00', 'Computer Sciences', '701', '1')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('104', '2.80', 'Computer Sciences', '302', '2')
Insert into Enroll(sid, grade, dname, cno, sectno)
values('104', '2.60', 'Sanitary Engineering', '561', '1')

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2012-10-11 : 09:05:48

quote:
Originally posted by Prosercunus

How do I delete all tables and foreign key restraints?

I know how to use the drop command and all that, but I am having an issue I can't figure out.

I made a database with 7 tables and everything worked fine, but I found a better way to make my 7 tables so I made a new query and created my tables.

Well I am getting

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__major__dname__563FA78C". The conflict occurred in database "master", table "dbo.dept", column 'dname'.

And Truncated errors.

How can I just delete everything off my SQL Server Management so I don't have these foreign key issues from other databases or tables?

--Here is the secript to drop all the foreign key constraints :

SELECT 'ALTER TABLE ' + SCHEMA_NAME(F.schema_id) + '.'
+ OBJECT_NAME(F.parent_object_id) + ' DROP CONSTRAINT ' + F.name
FROM SYS.FOREIGN_KEYS AS F
INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.constraint_object_id
--WHERE OBJECT_NAME(F.PARENT_OBJECT_ID) = 'YourObjectName'

once drop the foreign key constraints then you can easily drop the all tables which you want

--Irk

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2012-10-11 : 09:11:41

grade CHAR(1) -- is the problem

"String or binary data would be truncated.

you have declared grade as CHAR(1) but you trying to insert '3.00' which has more then one Char right.!!

--Irk

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-11 : 09:18:36

wow. I can't believe I missed that. Thanks a lot.

Everyone.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-12 : 06:41:15

You should REALLY REALLY consider re-creating all your tables in their own database. Master is not meant to be used.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Prosercunus
Starting Member

22 Posts

Posted - 2012-10-12 : 17:42:58

quote:
Originally posted by DonAtWork

You should REALLY REALLY consider re-creating all your tables in their own database. Master is not meant to be used.

Yep. I did exactly that a day or two ago.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources