| Author |
 Topic  |
|
|
jacksparrow
Starting Member
Bahrain
6 Posts |
 Posted - 12/21/2012 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
|
|
jacksparrow
Starting Member
Bahrain
6 Posts |
Posted - 12/21/2012 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/21/2012 : 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
India
47023 Posts |
Posted - 12/21/2012 : 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
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/21/2012 : 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
India
47023 Posts |
Posted - 12/21/2012 : 09:19:54
|
yes. But to be clearer its always better to specify it.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jacksparrow
Starting Member
Bahrain
6 Posts |
Posted - 12/21/2012 : 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
India
47023 Posts |
Posted - 12/21/2012 : 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
Bahrain
6 Posts |
Posted - 12/21/2012 : 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
India
47023 Posts |
Posted - 12/21/2012 : 09:42:51
|
yep..Oracle SQL,MYSQL,MS SQL are different
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jacksparrow
Starting Member
Bahrain
6 Posts |
Posted - 12/21/2012 : 09:46:22
|
| the answer you told me will work on oracle sql ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
|
|
jacksparrow
Starting Member
Bahrain
6 Posts |
Posted - 12/21/2012 : 11:46:19
|
| thanks alot visakh16 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/21/2012 : 11:49:31
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
got problems in creating tables with constraints
