Just started learning SQL recently.

But one thing i'm still not clear on is about altering relationships between
tables after they've been created.

Instead of creating a foreign key when the table is first created - i create
the table and then run a query to set the foreign key and relationship
(one-to-one, one-to-many etc)

Anyways, long story short is i want to create a one-to-one relationship with
a table but am having problems with adding more than one constraint at a
time when altering a table.

Understand yet? Easiest thing to do is show you:

I have 2 tables: Branch_Table and Employee_Table

I want to create a one-to-one relationship between emp_id on the
Branch_Table and manager_id on the Employee_Table.

The SQL i've written which doesn't seem to work is:

ALTER TABLE Branch_Table
Add Constraint Branch_Table_FK1 FOREIGN KEY (manager_id)
Add Constraint Branch_Table_UQ1 Unique (manager_id)
References Employee_Table (emp_id));

Am having trouble with that second Add constraint (UQ1 unique). I know it's
something to do with the Add syntax above.

So basically, my question is can i create a one-to-one relationship with
just the one SQL Query? And how would i do it?

Many thanks in advance.

Cheers for the reply Hugo.

But your syntax didn't work. It's giving me the same syntax error i've been
getting all along for that second add constraint.

I'm starting to think you can only add one constraint at a time in that
Alter Table syntax.

Anyone else have any suggestions / information on this?


[quoted text, click to view]

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Brian,

Try this instead. I didn't test it, but according to the syntax in Books
Online, it should work.

ALTER TABLE Branch_Table
Add Constraint Branch_Table_FK1 FOREIGN KEY (manager_id)
References Employee_Table (emp_id),
Add Constraint Branch_Table_UQ1 Unique (manager_id)


Best, Hugo
--

Brian Basquille (replytogroup@please.com) writes:
[quoted text, click to view]

As Hugo pointed out, you need a comma, but he missed that you have an ADD
too many. And you also have the UNIQUE constraint right in the middle of
the FK constraint. Finally, you have one parenthesis too many. Here is the
correct version

ALTER TABLE Branch_Table ADD
Constraint Branch_Table_FK1 FOREIGN KEY (manager_id)
References Employee_Table (emp_id)),
Constraint Branch_Table_UQ1 Unique (manager_id)

While the syntax graphs in Books Online may be difficult to start with -
even Hugo got lost there - there is no better way to learn the syntax
by studying them. It's certainly is a faster way than waiting for response
in newsgroups. (And if the syntax graphs are too bewildering, the example
at the bottom of each topic, can give you a head start.)



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

Books Online for SQL Server SP3 at

ALTER TABLE Branch_Table ADD
CONSTRAINT Branch_Table_FK1 FOREIGN KEY (manager_id)
REFERENCES Employee_Table (emp_id),
CONSTRAINT Branch_Table_UQ1 UNIQUE(manager_id) ;

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

Hi Brian,

I'm sorry, my fault. As Erland and David told you, I forgot to leave out
the second "add" keyword.

Best, Hugo
--

Thanks all!

Much appreciated!

Got an SQL Exam tomorrow at 2 - wish me luck!

Thanks again!