Anyone know much about SQL?

Basq

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.

Anima

Might get better results if you post in the programming or webmaster forum.

Basq

Anima wrote:

Might get better results if you post in the programming or webmaster forum.

Cheers for the advice Anima.

Thought i'd post it here as a longshot really. Already posted it in the DBForums (professional SQL forum) last night and no-one answered it! Obviously not that professional!

Posted it in a SQL newsgroup a little while ago and got a reply soon after. Don't think it can be done anyways (adding two constraints in the one SQL forum).

the_syco

Yeah, you can. Figured it out there during my last job. I'll post up some code later, when I find the file :cool:

[edit]
I can't use access Seems to be broken, so the following is from a drunken memory. If it doesn't make sense, PM me, and I'll answer when I'm sober.

3 tables; london, newyork, dublin_04
the "_" underscore between "dublin" and "04" is vital, as SQL sometimes see's it as two seperate categories, and not one table

each table has 4 fields that are the same; train, bus, car, plane

SELECT train, bus, car, plane
FROM london

UNION SELECT train, bus, car, plane
FROM newyork

UNION SELECT train, bus, car, plane
FROM dublin_04;

The ";" semi-colon at the end, means end-of-statement. Otherwise, you et an error.
The fields must be the same. To query, just do a normal query, using the SQL database as the source.
The UNION is put in front of SELECT for the 2nd table on.
There maybe an easier way to do so, but I don't know of it.


The above leaves out a great deal of info, so I'll get back to you. At the moment, I'm employed (ie; bored), so if you want to give me a small part of it, I'll fix them together, + give you comments, etc.

Otherwise, explain what you want, and I'll do my best to explain what needs to be done.
[/edit]