Assigning FOREIGN KEY, which method is correct?

Hi guys,

I am real new to SQL and just like to clear up some questions regarding to assigning FOREIGN KEY.

Are there any differences in assigning foreign keys this way:

CREATE TABLE NURSE
(EMP_ID VARCHAR2(8) CONSTRAINT fk_nurse_emp REFERENCES EMPLOYEE(EMP_ID),
WARD_ID VARCHAR2(8) CONSTRAINT fk_nurse_ward REFERENCES WARD(WARD_ID),
CONSTRAINT pk_nurse PRIMARY KEY(EMP_ID, WARD_ID));

and this way:

CREATE TABLE NURSE
(EMP_ID VARCHAR2(8),
WARD_ID VARCHAR2(8),
CONSTRAINT fk_nurse_emp FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(EMP_ID),
CONSTRAINT fk_nurse_ward FOREIGN KEY(WARD_ID) REFERENCES WARD(WARD_ID),
CONSTRAINT pk_nurse PRIMARY KEY(EMP_ID, WARD_ID));

What are the differences between them?

Many thanks.No difference - except that you couldn't create a composite foreign key with the first method. They are just alternatives.|||Thank you for clearing this up for me andrewst.

Another question, might not be all that important, but which is the preferred method?

I guess the second method is more preferred, as it clearly identifies the FOREIGN KEYS, but it takes more lines. I just like to learn the basic conventions first and stick to it.|||Well it's really a matter of personal taste and/or company policy. But since the second syntax works for all foreign keys and the first works only for single-column foreign keys, the second syntax could be preferred.

Assigning Foreign Key To New SQL Server Table

Hi

I am creating new SQL Server Tables using SQL Server 2005. I have set
primary key to the tables .But I do not know how to assign Foreign key
to the tables .I need to do some joins later and that is why I have to
put Foreign key to the table . The Primary key is visible and can be
assigned easily .But How do I assign foreign key .

Thanks

*** Sent via Developersdex http://www.developersdex.com ***.. . (kmandal@.sark.com) writes:
> I am creating new SQL Server Tables using SQL Server 2005. I have set
> primary key to the tables .But I do not know how to assign Foreign key
> to the tables .I need to do some joins later and that is why I have to
> put Foreign key to the table . The Primary key is visible and can be
> assigned easily .But How do I assign foreign key .

First of all, questions about SQL 2005 are best asked in the SQL 2005
newsgroups, as these are monitored by the SQL Server deverlopers. Access
info here: http://go.microsoft.com/fwlink/?linkid=31765

As for your question, the syntax is as in this example:

ALTER TABLE tbl ADD CONSTRAINT fk_myforeignley (col1, col2)
REFERENCES othertbl (col1, col2)

Or were you using the table designer? I recommend that you learn the
syntax to create table from SQL statements. In the long run that will
make you more effective and productive, than clicking around in the
table designer. Also, there are several *serious* bugs in the table
designer when it comes to modify existing tables, so the less you use
it, the better.

(If you are dead set on it, I believe that if you right-click there
are foreign keys in the context menu.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Hi

Thanks for your reply . It worked and I was able to assign Foreign key
to the tables . Thanks again .

*** Sent via Developersdex http://www.developersdex.com ***|||Foreign keys will definitely help with relational integrity. However,
for the purposes of joining the foreign key need not be pre-defined.
That is what the join syntax in the select statement is for.