got problems in creating tables with constraints

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

got problems in creating tables with constraints

Author

Topic

jacksparrow
Starting Member

6 Posts

Posted - 2012-12-21 : 08:17:56

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-21 : 08:27:01

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

jacksparrow
Starting Member

6 Posts

Posted - 2012-12-21 : 08:30:36

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 ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-21 : 08:44:30

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 09:03:02

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/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-21 : 09:09:03

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 09:19:54

yes. But to be clearer its always better to specify it.

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

jacksparrow
Starting Member

6 Posts

Posted - 2012-12-21 : 09:32:30

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 09:35:19

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/

jacksparrow
Starting Member

6 Posts

Posted - 2012-12-21 : 09:41:54

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 09:42:51

yep..Oracle SQL,MYSQL,MS SQL are different

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

jacksparrow
Starting Member

6 Posts

Posted - 2012-12-21 : 09:46:22

the answer you told me will work on oracle sql ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 09:52:38

see how to declare fk in oracle

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

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

jacksparrow
Starting Member

6 Posts

Posted - 2012-12-21 : 11:46:19

thanks alot visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 11:49:31

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources