SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 got problems in creating tables with constraints
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jacksparrow
Starting Member

Bahrain
6 Posts

Posted - 12/21/2012 :  08:17:56  Show Profile  Reply with Quote
hello
good morning
am new to sql forum and i hope to learn and get tips and experience from the expert here

i did create many tables with values but the problem is i got a constraint problems about primary and foreign key .. i don't know how to solve it because i think the syntax is correct

i put them in text file , check it out is my syntax wrong ? what should i edit to make tables work ?
http://speedy.sh/JdPcY/TablesValues.txt

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/21/2012 :  08:27:01  Show Profile  Reply with Quote
your link is restricted in my company...
So i couldn't see your script...

Follow this link to know constraints syntax
http://msdn.microsoft.com/en-us/library/ms189862(v=sql.105).aspx

--
Chandu
Go to Top of Page

jacksparrow
Starting Member

Bahrain
6 Posts

Posted - 12/21/2012 :  08:30:36  Show Profile  Reply with Quote
i check them i have little different

i create table branch

CREATE TABLE Branch
(
Branch_No number(10) constraint branchno_pk PRIMARY KEY NOT NULL,
Location_Branch VARCHAR(20),
Telephone_Branch VARCHAR(20),
Address VARCHAR(50)

);

and then i create table employee

CREATE TABLE Employee
(
Emp_ID number(10) constraint empid_pk PRIMARY KEY NOT NULL,
Branch_No number constraint branchid_fk FOREIGN KEY (Branch_No) references Branch,
Emp_Name VARCHAR(50),
Emp_Adress VARCHAR(50),
Emp_Phone VARCHAR(20),
Emp_NationalID VARCHAR(20),
Emp_Salary int,
Emp_Vacition INT

);

but they both are not working .. what is wrong with my constraint ?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/21/2012 :  08:44:30  Show Profile  Reply with Quote
Hi,
number datatype is not availale in SQL Server. You can use INT instead....



CREATE TABLE Branch
(
Branch_No int constraint branchno_pk PRIMARY KEY NOT NULL,
Location_Branch VARCHAR(20),
Telephone_Branch VARCHAR(20),
Address VARCHAR(50)

);


CREATE TABLE Employee
(
Emp_ID int constraint empid_pk PRIMARY KEY NOT NULL,
Branch_No int constraint branchid_fk FOREIGN KEY (Branch_No) references Branch,
Emp_Name VARCHAR(50),
Emp_Adress VARCHAR(50),
Emp_Phone VARCHAR(20),
Emp_NationalID VARCHAR(20),
Emp_Salary int,
Emp_Vacition INT
);

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  09:03:02  Show Profile  Reply with Quote
your foreign key definition should be below


CREATE TABLE Employee
(
Emp_ID int constraint empid_pk PRIMARY KEY NOT NULL, 
Branch_No int constraint branchid_fk FOREIGN KEY references Branch(Branch_No),
Emp_Name VARCHAR(50),
Emp_Adress VARCHAR(50),
Emp_Phone VARCHAR(20),
Emp_NationalID VARCHAR(20),
Emp_Salary int,
Emp_Vacition INT
);


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/21/2012 :  09:09:03  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

your foreign key definition should be below


CREATE TABLE Employee
(
Emp_ID int constraint empid_pk PRIMARY KEY NOT NULL, 
Branch_No int constraint branchid_fk FOREIGN KEY references Branch(Branch_No),
Emp_Name VARCHAR(50),
Emp_Adress VARCHAR(50),
Emp_Phone VARCHAR(20),
Emp_NationalID VARCHAR(20),
Emp_Salary int,
Emp_Vacition INT
);


Hi visakh,
If there is only one primary key in parent table, then that is optional... right?

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  09:19:54  Show Profile  Reply with Quote
yes. But to be clearer its always better to specify it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jacksparrow
Starting Member

Bahrain
6 Posts

Posted - 12/21/2012 :  09:32:30  Show Profile  Reply with Quote
thanks my friend for help but i check it out http://sqlfiddle.com

the branch table i got this error

Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint branchno_pk PRIMARY KEY NOT NULL,
Location_Branch VARCHAR' at line 3:

the second one after editing the foreign key constraint

Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint empid_pk PRIMARY KEY NOT NULL,
Branch_No int constraint branchid_fk ' at line 3:

i don't know what to do ! seriously help me in these situation my friend ;s
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  09:35:19  Show Profile  Reply with Quote
Oh...so you're using MySQL. Then you're in wrong forum. please post in relevant forums like www.dbforums.com for more help. We deal with only MS SQL Server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jacksparrow
Starting Member

Bahrain
6 Posts

Posted - 12/21/2012 :  09:41:54  Show Profile  Reply with Quote
am using oracle sql but because i don't have the program install in my pc .. are they different ? oracle sql and my sql ? ;s
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  09:42:51  Show Profile  Reply with Quote
yep..Oracle SQL,MYSQL,MS SQL are different


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jacksparrow
Starting Member

Bahrain
6 Posts

Posted - 12/21/2012 :  09:46:22  Show Profile  Reply with Quote
the answer you told me will work on oracle sql ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  09:52:38  Show Profile  Reply with Quote
see how to declare fk in oracle

http://www.techonthenet.com/oracle/foreign_keys/foreign_keys.php

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jacksparrow
Starting Member

Bahrain
6 Posts

Posted - 12/21/2012 :  11:46:19  Show Profile  Reply with Quote
thanks alot visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  11:49:31  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000