Beginner question on Altering constraints in Tables..

Hello all,

Just started learning SQL recently.

But one thing i'm still not clear on is alter tables relationships 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.This syntax may vary between DBMSs, but this works in Oracle:

ALTER TABLE Branch_Table
Add (Constraint Branch_Table_UQ1 Unique (manager_id),
Constraint Branch_Table_FK1 FOREIGN KEY (manager_id)
References Employee_Table (emp_id));
(You had the REFERENCES clause on the wrong constraint, by the way).

before delete triggers in MSSQL

Hi
I've got several tables that have foreign key relationships with a 'users'
table - for example tasks (assigned to user) and customers (liason of
customer). When I delete a user, I would like to set all the foreignkeyed
rows to have null as a user, rather than doing a cascading delete. This
could be done in a stored procedure, but the problem is that the application
has multiple modules that can be added and removed, so I don't know at
execution time what tables there are. The only way to do this I could think
of was with triggers. I know this is supposed to be a big no no, but
couldn't think of anything else. Not that it matters, because triggers won't
work here as the trigger is fired after the delete is done and hence bombs
out due to violated constraint checking. I can't use an 'INSTEAD OF' trigger
unfortunately as I need the facility to have multiple triggers. I see that
Oracle has a BEFORE trigger, imagining that this would solve the problem. Is
there similar functionality in SQL, or another way to do this?
I was hoping that this is a common task and that there is an easy way to do
it, but no luck so far with searches
Thanks
JoeOn Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:

> The only way to do this I could think
>of was with triggers. I know this is supposed to be a big no no, but
>couldn't think of anything else.
Hi Joe,
Why do you think triggers are a big no no? Of course, they shouldn't be
your first option and you should prefer DRI over triggers where possible,
but there are situations where triggers are an invaluable instrument.

> Not that it matters, because triggers won't
>work here as the trigger is fired after the delete is done and hence bombs
>out due to violated constraint checking.
That's correct. You either have to remove the foreign key constraint and
do the checking in the trigger as well, or you have to use INSTEAD OF
triggers.

> I can't use an 'INSTEAD OF' trigger
>unfortunately as I need the facility to have multiple triggers.
Maybe I'm missing something, but why don't you just combine the actions of
those various triggers into one trigger?

> I see that
>Oracle has a BEFORE trigger, imagining that this would solve the problem. I
s
>there similar functionality in SQL,
The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
has to offer.

> or another way to do this?
As I already indicated, you could move the constraint checking to the
trigger as well. But that's a bad idea, since that would force you to
write and maintain more trigger code, it would slow things down and it
would deny the query optimizer the knowledge of this constraint, so that
it can't use this knowledge to optimize query execution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to agree 100% with Hugo. Use instead of triggers, or drop your
relationships and implement them in triggers (which will be just as good,
but will be pretty painful to implement consiering you can just do it in the
instead of trigger.) You can have as many actions in the trigger as you
want.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cva111hh4p6m600c28jagd6m348r6g2jii@.
4ax.com...
> On Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
>
> Hi Joe,
> Why do you think triggers are a big no no? Of course, they shouldn't be
> your first option and you should prefer DRI over triggers where possible,
> but there are situations where triggers are an invaluable instrument.
>
> That's correct. You either have to remove the foreign key constraint and
> do the checking in the trigger as well, or you have to use INSTEAD OF
> triggers.
>
> Maybe I'm missing something, but why don't you just combine the actions of
> those various triggers into one trigger?
>
> The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
> has to offer.
>
> As I already indicated, you could move the constraint checking to the
> trigger as well. But that's a bad idea, since that would force you to
> write and maintain more trigger code, it would slow things down and it
> would deny the query optimizer the knowledge of this constraint, so that
> it can't use this knowledge to optimize query execution.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)