469,924 Members | 1,418 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,924 developers. It's quick & easy.

Syntax to Add 2 constraints using Alter Table?

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.

Jul 20 '05 #1
6 11823
On Sat, 6 Nov 2004 23:33:08 -0000, Brian Basquille wrote:

(snip)
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?


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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
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?
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:qg********************************@4ax.com...
On Sat, 6 Nov 2004 23:33:08 -0000, Brian Basquille wrote:

(snip)
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?


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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Jul 20 '05 #3
Brian Basquille (re**********@please.com) writes:
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));


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
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
--
Jul 20 '05 #5
On Sun, 7 Nov 2004 00:38:41 -0000, Brian Basquille wrote:
Cheers for the reply Hugo.

But your syntax didn't work.


Hi Brian,

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

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #6
Thanks all!

Much appreciated!

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

Thanks again!

Jul 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Heist | last post: by
1 post views Thread by ManningFan | last post: by
1 post views Thread by obastard | last post: by
2 posts views Thread by Disha | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.