how to do it - SQL Server Forums

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

how to do it

Author

Topic

chinnanu143
Starting Member

13 Posts

Posted - 2010-11-22 : 16:20:03

hi these are tables from book iam new and want practise more to get perfection well quetion is

here are 4 table which related to each other

if i create table like this it generates error because of foreign key relations..so how to do it with out error

CREATE TABLE EMPLOYEES (EMPLOYEEID NUMERIC(9) PRIMARY KEY,FIRSTNAME VARCHAR(10),LASTNAME VARCHAR(20),DEPTCODE CHAR(5) FOREIGN KEY (DEPTCODE) REFERENCES DEPARTMENTS(CODE),SALARY NUMERIC(9,2))

CREATE TABLE DEPARTMENTS(CODE CHAR(5) PRIMARY KEY,NAME VARCHAR(30),MANAGERID NUMERIC(9) FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEES(EMPLOYEEID),SUBDEPTOF CHAR(5) FOREIGN KEY (SUBDEPTOF) REFERENCES DEPARTMENTS(CODE))

CREATE TABLE PROJECTS (PROJECTID CHAR(8) PRIMARY KEY,DEPTCODE CHAR(5) FOREIGN KEY (DEPTCODE) REFERENCES DEPATMENTS(CODE),DESCRIPTION VARCHAR(200),STARTDATE DATE,ENDDATE DATE,REVENUE NUMERIC(12,2))

CREATE TABLE WORKSON(EMPLOYEEID NUMERIC(9) FOREIGN KEY (EMPLOYEEID) REFERENCES EMPLOYEES(EMPOYEEID),PROJECTID CHAR(8) FOREIGN KEY (PROJECTID) REFERENCES PROJECTS(PROJECTID),ASSIGNEDTIME DECIMAL(3,2))

can i create table first add all values later then give relations/keys

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-22 : 16:28:15

That's the usual way

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 16:30:25

yes - or you can change the order of the creates

Actually you have a circular reference
Departments references employees references departments
So you could never insert a row into either table without a foreign key error unless you allowed nulls to avoid the check.

To add fk after table create
alter table xxx add constraint xxxx foreign key (xxx) references xxxx (xxx)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-22 : 16:31:28

quote:
Originally posted by chinnanu143

can i create table first add all values later then give relations/keys

That is one way. The other, more proper, way is to create the tables. Then create the relationships/keys. Finally, you add the data to the tables so that DIR (declarative referential integrity) can be enforced.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-22 : 16:39:36

You're assuming that hey will be doing loads...

And it just amazes me that constraints aren't applied at that point

DB2 will spit out rows that fail the constraint checks if you tell it to...and not if you tell it so (CHECKNO) then you need to repair the tables after the load and it will tell you then

You shouldn't be able to just load data and have bad data in there

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-22 : 16:51:22

quote:
Originally posted by X002548

You're assuming that hey will be doing loads...

The OP Said: "can i create table first add all values later then give relations/keys"

Not much of an ssumption there. Besides, what else are you going to do with empty tables?

chinnanu143
Starting Member

13 Posts

Posted - 2010-11-23 : 06:34:46

quote:
Originally posted by Lamprey

quote:
Originally posted by chinnanu143

can i create table first add all values later then give relations/keys
That is one way. The other, more proper, way is to create the tables. Then create the relationships/keys. Finally, you add the data to the tables so that DIR (declarative referential integrity) can be enforced.

yeah this is correct way...in this way only we can have use fk

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources