Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem my script seem to error a bit. Could you fix my script ?

here one pic:

http://s1042.photobucket.com/user/dao1001/media/1_zpsmkfzy4p1.png.html

and this is a create 5 tables with the right attributes and constraints here.

http://s1042.photobucket.com/user/dao1001/media/3_zpsobbnpbnm.jpg.html

and then also I have more thing there after that...

Thanks

What I have tried:

SQL
/* to create an employee table */

CREATE TABLE employee
(
	EmpId		char(2)		PRIMARY KEY,
	FirstName	varchar(20)	NOT NULL,
	LastName	varchar(20)	NOT NULL,
	Gender		char(2)		CHECK(gender IN ('M', 'F')),
	DateJoined	date		NOT NULL,
	DateLeft	date,
	Contract	varchar(4)	NOT NULL);

/* to create task table */

CREATE TABLE task
(
	TaskId		char(4)		PRIMARY KEY,
	TaskName	varchar(25)	NOT NULL,
	GivenDate	date		NOT NULL,
	StartDate	date,
	EndDate		date,
	MaxHours	number(3,1)	DEFAULT 20.0 NOT NULL);

/* to create computer table */

CREATE TABLE computer
(
	SerialNum	char(7)		PRIMARY KEY,
	Make		varchar(12)	NOT NULL,
	Model		varchar(20)	NOT NULL,
	ProcessorType	varchar(20)	NOT NULL,
	ProcessorSpeed	number(4,2)	NOT NULL,
	RAM		char(7),
	DiskSize	char(6));

/* to create job table */

CREATE TABLE job

(
	TaskId		char(4)		PRIMARY KEY, FOREIGN KEY,
	EmpId		char(2)		PRIMARY KEY, FOREIGN KEY,
	HoursSpent	number(3,1)	DEFAULT 20.0 NOT NULL);

/* to create employee computer table */

CREATE TABLE emp_computer
(
	SerialNum	char(7)		PRIMARY KEY, FOREIGN KEY,
	EmpId		char(2)		PRIMARY KEY, FOREIGN KEY,
	DateAssigned	date		NOT NULL,
	Notes		varchar(50));
Posted
Updated 4-Oct-16 22:37pm
v4
Comments
Tomas Takac 5-Oct-16 2:53am    
What is the error message you get? You cannot create primary and foreign keys like that.

1 solution

Assuming you are using SQL Server. There are number of problems in your script.
1) The data type is numeric(x,y) not number(x,y)
2) You can only specify PRIMARY KEY constraint once. Do not use the inline definition if you need compound primary key.
3) Your foreign keys need to specify the target table and column.
4) Use named constraints, it will save you lots of trouble later.

Here is how it should look like:
SQL
CREATE TABLE job
(
	TaskId		char(4) NOT NULL,
	EmpId		char(2) NOT NULL,
	HoursSpent	numeric(3,1) NOT NULL CONSTRAINT DF_job_HoursSpent DEFAULT (20.0),

	CONSTRAINT PK_job PRIMARY KEY (TaskId, EmpId),
	CONSTRAINT FK_job_task FOREIGN KEY (TaskId) REFERENCES task (TaskId),
	CONSTRAINT FK_job_empoyee FOREIGN KEY (EmpId) REFERENCES employee (EmpId)
);
 
Share this answer
 
Comments
bearforever99 5-Oct-16 7:10am    
CONSTRAINT DF_job_HoursSpent DEFAULT (20.0), what is meant? I think it say "should be between 0 and 40 from task table.
Tomas Takac 5-Oct-16 14:10pm    
That is your default constraint, I just added a name to it. A default constraint, as the name suggests, provides a default value in case you don't specify value for that column during insert. Maybe you are looking for a CHECK constraint? Read the documentation for CREATE TABLE (Transact-SQL)[^].

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900