Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
it
inside
>
>.
>

Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
--
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
>> Hi,
>> This could be a basic question. But still I want to get
it
>> clarified.
>> Normally if an ALTER or UPDATE command been tried with a
>> non existing column, SQL Server throws error. To avoid
>> this, the practice is to place the alter statements
inside
>> an IF EXISTS() block. Only if the column exists/not
>> exists, the alter would get executed.
>> I tried using the same technique for an UPDATE. I still
>> get the error inspite I have an exists() check.
>> In the below given example, The statement1 (ALTER) does
>> not throw any error while the statement2(Update)
>> throws error.
>> Could somebody please explain the reason behind this?
>> Regards,
>> J.P. Job
>>
>> Eg:.
>> USE PUBS
>> go
>> --Statement1
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> ALTER TABLE AUTHORS DROP COLUMN DUMMY
>> go
>> --Statement2
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> Update AUTHORS
>> SET DUMMY = 'TEST'
>> go
>
>.
>

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