We help IT Professionals succeed at work.

Creating a database and tables in SQL Server

516 Views
Last Modified: 2012-08-13
Hi,

I want to create a database called plaincart and some tables to go with it.  If the database exists, it is not created.  If a table exists, it is dropped and re-created.

I am using SQL Server 2005.

When I execute this code I get an error saying "syntax error near IF".  I tried surrounding the IF NOT EXISTS with [], but then I get an error with Use plaincart.
CREATE DATABASE IF NOT EXISTS plaincart;
USE plaincart;
 
 
 
DROP TABLE IF EXISTS `tbl_cart`;
CREATE TABLE `tbl_cart` (
  `ct_id` int(10) unsigned NOT NULL auto_increment,
  `pd_id` int(10) unsigned NOT NULL default '0',
  `ct_qty` mediumint(8) unsigned NOT NULL default '1',
  `ct_session_id` char(32) NOT NULL default '',
  `ct_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`ct_id`),
  KEY `pd_id` (`pd_id`),
  KEY `ct_session_id` (`ct_session_id`)
) TYPE=MyISAM AUTO_INCREMENT=58 ;
 
 
 
DROP TABLE IF EXISTS `tbl_category`;
CREATE TABLE `tbl_category` (
  `cat_id` int(10) unsigned NOT NULL auto_increment,
  `cat_parent_id` int(11) NOT NULL default '0',
  `cat_name` varchar(50) NOT NULL default '',
  `cat_description` varchar(200) NOT NULL default '',
  `cat_image` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`cat_id`),
  KEY `cat_parent_id` (`cat_parent_id`),
  KEY `cat_name` (`cat_name`)
) TYPE=MyISAM AUTO_INCREMENT=18 ;
 
 
 
INSERT INTO `tbl_category` (`cat_id`, `cat_parent_id`, `cat_name`, `cat_description`, `cat_image`) VALUES (17, 13, 'Hunter X Hunter', 'Story about hunter and combat', '');
INSERT INTO `tbl_category` (`cat_id`, `cat_parent_id`, `cat_name`, `cat_description`, `cat_image`) VALUES (12, 0, 'Cars', 'Expensive and luxurious cars', 'dce08605333d805106217aaab7f93b95.jpg');
INSERT INTO `tbl_category` (`cat_id`, `cat_parent_id`, `cat_name`, `cat_description`, `cat_image`) VALUES (13, 0, 'Manga', 'It''s all about manga, yay....', '2a5d7eb60c1625144b3bd785bf70342c.jpg');
INSERT INTO `tbl_category` (`cat_id`, `cat_parent_id`, `cat_name`, `cat_description`, `cat_image`) VALUES (14, 12, 'Volvo', 'Swedish luxury car', '');
INSERT INTO `tbl_category` (`cat_id`, `cat_parent_id`, `cat_name`, `cat_description`, `cat_image`) VALUES (15, 12, 'Mercedes-Benz', 'Expensive but real good', '');
INSERT INTO `tbl_category` (`cat_id`, `cat_parent_id`, `cat_name`, `cat_description`, `cat_image`) VALUES (16, 13, 'Naruto', 'This is the story of Naruto and all his gang', '');
 
 
DROP TABLE IF EXISTS `tbl_currency`;
CREATE TABLE `tbl_currency` (
  `cy_id` int(10) unsigned NOT NULL auto_increment,
  `cy_code` char(3) NOT NULL default '',
  `cy_symbol` varchar(8) NOT NULL default '',
  PRIMARY KEY  (`cy_id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
 
 
 
INSERT INTO `tbl_currency` (`cy_id`, `cy_code`, `cy_symbol`) VALUES (1, 'EUR', '€');
INSERT INTO `tbl_currency` (`cy_id`, `cy_code`, `cy_symbol`) VALUES (2, 'GBP', '£');
INSERT INTO `tbl_currency` (`cy_id`, `cy_code`, `cy_symbol`) VALUES (3, 'JPY', '¥');
INSERT INTO `tbl_currency` (`cy_id`, `cy_code`, `cy_symbol`) VALUES (4, 'USD', '$');
 
 
DROP TABLE IF EXISTS `tbl_order`;
CREATE TABLE `tbl_order` (
  `od_id` int(10) unsigned NOT NULL auto_increment,
  `od_date` datetime default NULL,
  `od_last_update` datetime NOT NULL default '0000-00-00 00:00:00',
  `od_status` enum('New', 'Paid', 'Shipped','Completed','Cancelled') NOT NULL default 'New',
  `od_memo` varchar(255) NOT NULL default '',
  `od_shipping_first_name` varchar(50) NOT NULL default '',
  `od_shipping_last_name` varchar(50) NOT NULL default '',
  `od_shipping_address1` varchar(100) NOT NULL default '',
  `od_shipping_address2` varchar(100) NOT NULL default '',
  `od_shipping_phone` varchar(32) NOT NULL default '',
  `od_shipping_city` varchar(100) NOT NULL default '',
  `od_shipping_state` varchar(32) NOT NULL default '',
  `od_shipping_postal_code` varchar(10) NOT NULL default '',
  `od_shipping_cost` decimal(5,2) default '0.00',
  `od_payment_first_name` varchar(50) NOT NULL default '',
  `od_payment_last_name` varchar(50) NOT NULL default '',
  `od_payment_address1` varchar(100) NOT NULL default '',
  `od_payment_address2` varchar(100) NOT NULL default '',
  `od_payment_phone` varchar(32) NOT NULL default '',
  `od_payment_city` varchar(100) NOT NULL default '',
  `od_payment_state` varchar(32) NOT NULL default '',
  `od_payment_postal_code` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`od_id`)
) TYPE=MyISAM AUTO_INCREMENT=1001 ;
 
 
 
 
DROP TABLE IF EXISTS `tbl_order_item`;
CREATE TABLE `tbl_order_item` (
  `od_id` int(10) unsigned NOT NULL default '0',
  `pd_id` int(10) unsigned NOT NULL default '0',
  `od_qty` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`od_id`,`pd_id`)
) TYPE=MyISAM;
 
 
 
DROP TABLE IF EXISTS `tbl_product`;
CREATE TABLE `tbl_product` (
  `pd_id` int(10) unsigned NOT NULL auto_increment,
  `cat_id` int(10) unsigned NOT NULL default '0',
  `pd_name` varchar(100) NOT NULL default '',
  `pd_description` text NOT NULL,
  `pd_price` decimal(9,2) NOT NULL default '0.00',
  `pd_qty` smallint(5) unsigned NOT NULL default '0',
  `pd_image` varchar(200) default NULL,
  `pd_thumbnail` varchar(200) default NULL,
  `pd_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `pd_last_update` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`pd_id`),
  KEY `cat_id` (`cat_id`),
  KEY `pd_name` (`pd_name`)
) TYPE=MyISAM AUTO_INCREMENT=22 ;
 
 
 
 
DROP TABLE IF EXISTS `tbl_shop_config`;
CREATE TABLE `tbl_shop_config` (
  `sc_name` varchar(50) NOT NULL default '',
  `sc_address` varchar(100) NOT NULL default '',
  `sc_phone` varchar(30) NOT NULL default '',
  `sc_email` varchar(30) NOT NULL default '',
  `sc_shipping_cost` decimal(5,2) NOT NULL default '0.00',
  `sc_currency` int(10) unsigned NOT NULL default '1',
  `sc_order_email` enum('y','n') NOT NULL default 'n'
) TYPE=MyISAM;
 
 
 
INSERT INTO `tbl_shop_config` (`sc_name`, `sc_address`, `sc_phone`, `sc_email`, `sc_shipping_cost`, `sc_currency`, `sc_order_email`) VALUES ('PlainCart - Just a plain online shop', 'Old warehouse under the bridge,\r\nWater Seven, Grand Line', '777-FRANKY', 'franky@tomsworkers.com', 5.00, 4, 'y');
 
 
 
DROP TABLE IF EXISTS `tbl_user`;
CREATE TABLE `tbl_user` (
  `user_id` int(10) unsigned NOT NULL auto_increment,
  `user_name` varchar(20) NOT NULL default '',
  `user_password` varchar(32) NOT NULL default '',
  `user_regdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user_name` (`user_name`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
 
 
INSERT INTO `tbl_user` (`user_id`, `user_name`, `user_password`, `user_regdate`, `user_last_login`) VALUES (1, 'admin', '43e9a4ab75570f5b', '2005-02-20 17:35:44', '2005-03-02 21:00:14');
INSERT INTO `tbl_user` (`user_id`, `user_name`, `user_password`, `user_regdate`, `user_last_login`) VALUES (3, 'webmaster', '026cf3fc6e903caf', '2005-03-02 17:52:51', '0000-00-00 00:00:00');

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
it should be more in the form of this:

IF EXISTS(select 1 from sys.databases where name = 'tbl_cart')
drop database tbl_cart

Author

Commented:
Ok, now I am getting errors with  "ct_id int(10) unsigned NOT NULL auto_increment"

am I not writing this correctly?  
IF EXISTS(select * from sys.databases where name="phpwebco_shop") 
USE phpwebco_shop;
 
 
IF EXISTS(select * from sys.databases where name="tbl_cart") 
DROP TABLE tbl_cart;
CREATE TABLE tbl_cart (
  ct_id int(10) unsigned NOT NULL auto_increment,
  pd_id int(10) unsigned NOT NULL default '0',
  ct_qty mediumint(8) unsigned NOT NULL default '1',
  ct_session_id char(32) NOT NULL default '',
  ct_date datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`ct_id`),
  KEY `pd_id` (`pd_id`),
  KEY `ct_session_id` (`ct_session_id`)
) TYPE=MyISAM AUTO_INCREMENT=58 ;

Open in new window

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks!

Author

Commented:
hey, one final question:

I am getting the error "The conversion of a char data type to a datatime data type resulted in an out-of-range datetime value." The statement has been terminated.

To me, it looks like I have the data correctly matching up...
IF Object_ID('phpwebco_shop.dbo.tbl_user', 'U') IS NOT NULL 
DROP TABLE tbl_user;
CREATE TABLE tbl_user (
  user_id int NOT NULL IDENTITY(1,1),
  user_name varchar(20) NOT NULL default '',
  user_password varchar(32) NOT NULL default '',
  user_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  user_last_login datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (user_id),
  
) 
 
 
INSERT INTO tbl_user ( user_name, user_password, user_regdate, user_last_login) VALUES ( 'admin', '43e9a4ab75570f5b', '2005-02-20 17:35:44', '2005-03-02 21:00:14');
INSERT INTO tbl_user ( user_name, user_password, user_regdate, user_last_login) VALUES ( 'webmaster', '026cf3fc6e903caf', '2005-03-02 17:52:51', '0000-00-00 00:00:00');

Open in new window

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
'0000-00-00 00:00:00' isn't a valid date.

Author

Commented:
but even if I change the default to '2005-02-20 17:35:44' I still get the same error.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
you're inserting that specific value

INSERT INTO tbl_user ( user_name, user_password, user_regdate, user_last_login) VALUES ( 'webmaster', '026cf3fc6e903caf', '2005-03-02 17:52:51', '0000-00-00 00:00:00');

Author

Commented:
thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.