Relationships between two tables

[ericwi]

Hi all,

I was tasked to modify my current table to correlate with another table. I have a table ( say table1 ) with a PK ( pri_code ). I need to correlate this PK with another table which ,incidentally, has the same name ( ie pri_code ).May I know how it is done?

I was thinking of 'alter table' command but some how it does not work.

Please advice. Thanks

 

[FlashMerlot]

Not sure I understand your problem. Are you trying to collapse two tables (both with the same PK) into one single table?

 

[jbenson001]

Check the syntax in books on line. Also, you can do it within a database diagram if you want to do it visually.

Jim

 

[ericwi]

I'm sorry to confuse all. I will try to rephrase it. I have created two tables. Both tables use the same name for one of the attribute( ie student_code for table1 and table2 ). Furthermore, both these tables use it as their primary key.

Now, my boss wants the primary key of one table to reference the primary key of the other table. He also wants the latter to accept the values from the former in the way that if the primary key of the former table has a null value in one of the records, it will not be inserted into the latter.

I hope I am not confusing anyone with this explanation.

Any advice appreciated.

 

[jbenson001]

A primary Key cannot accept nulls.

 

[ericwi]

Hi all,

I have yet to try this statement:-

alter table coursefee
add check ( course_code = course.course_code and
course.quota is not null );

coursefee :- table1
course_code :- PK of table1
course :- table2
course.course_code :- PK of table2
course.quota :- another attribute of table2

My intention is set a constraint on table1 where there will be an error if there is an insert on table2 whereby quota is null.


Is this plausible?

Sorry to trouble all again.