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
 Why I am not able to create this table

Author  Topic 

putturps
Starting Member

2 Posts

Posted - 2013-01-11 : 02:18:46
Hi Experts,

I am trying to create below table, but getting some error, some one could you please advice, since i am trying this using multiple FK constraints, will that be any problem??

CREATE TABLE JOB_HISTORY
(EMPLOYEE_ID NUMBER(6) CONSTRAINTS EMP_ID_PK PRIMARY KEY,
START_DATE DATE CONSTRAINTS START_DATE_NN NOT NULL,
END_DATE DATE CONSTRAINTS END_DATE_NN NOT NULL,
JOB_ID VARCHAR2(10),
CONSTRAINTS JOB_ID_FK FOREIGN KEY (JOB_ID) REFERENCE JOBS(JOB_ID),
DEPARTMENT_ID NUMBER(4),
CONSTRAINTS DEP_ID_FK FOREGIN KEY (DEPARTMENT_ID) REFERENCE DEPARTMENT(DEPARTMENT_ID));

INSERT INTO JOB_HISTORY VALUES(102, '13-JAN-93', '24-JUL-98', 'IT_PROG', 60);
INSERT INTO JOB_HISTORY VALUES(101, '21-SEP-89', '27-OCT-93', 'AC_ACCOUNT', 110);
INSERT INTO JOB_HISTORY VALUES(101, '28-OCT-93', '15-MAR-97', 'AC_MGR', 110);
INSERT INTO JOB_HISTORY VALUES(201, '17-FEB-96', '19-DEC-99', 'MK_REP', 20);
INSERT INTO JOB_HISTORY VALUES(114, '24-MAR-98', '31-DEC-99', 'ST_CLERK', 50);
INSERT INTO JOB_HISTORY VALUES(122, '01-JAN-99', '31-DEC-99', 'ST_CLERK', 50);
INSERT INTO JOB_HISTORY VALUES(200, '17-SEP-87', '17-JUN-93', 'AD_ASST', 90);
INSERT INTO JOB_HISTORY VALUES(176, '24-MAR-98', '31-DEC-98', 'SA_REP', 80);
INSERT INTO JOB_HISTORY VALUES(176, '01-JAN-99', '31-DEC-99', 'SA_MAN', 80);
INSERT INTO JOB_HISTORY VALUES(200, '01-JUL-94', '31-DEC-98', 'AC_ACCOUNT', 90);

Putta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:01:40
is this t-sql?
Number is not a datatype in T-sql
also PRIMARY KEY should be NOT NULL

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

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-11 : 03:24:29
Also
1) VARCHAR2 is NOT a datatype in MSSQL..
2) put REFERENCES instead of REFERENCE
3) put CONSTRAINT instead of CONSTRAINTS
4) correct the spelling of FOREIGN

CREATE TABLE JOB_HISTORYs
(EMPLOYEE_ID int CONSTRAINT EMP_ID_PK PRIMARY KEY,
START_DATE DATE CONSTRAINT START_DATE_NN NOT NULL,
END_DATE DATE CONSTRAINT END_DATE_NN NOT NULL,
JOB_ID VARCHAR(10),
CONSTRAINT JOB_ID_FK FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID),
DEPARTMENT_ID int,
CONSTRAINT DEP_ID_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID));



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:25:49
I think OP is using some other RDBMS

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

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-11 : 04:28:52
What it may be.... those REFERENCES, CONSTRAINT, FOREIGN are typos of OP

Follow this link to create table in any Database:
http://www.w3schools.com/sql/sql_foreignkey.asp
--
Chandu
Go to Top of Page

putturps
Starting Member

2 Posts

Posted - 2013-01-12 : 01:44:11
Thank Chandu,......



Putta
Go to Top of Page
   

- Advertisement -