473,883 Members | 1,686 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_FK 1 FOREIGN KEY (manager_id)
Add Constraint Branch_Table_UQ 1 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 12096
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_FK 1 FOREIGN KEY (manager_id)
Add Constraint Branch_Table_UQ 1 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_FK 1 FOREIGN KEY (manager_id)
References Employee_Table (emp_id),
Add Constraint Branch_Table_UQ 1 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_rFa ct.in_SPAM_fo> wrote in message
news:qg******** *************** *********@4ax.c om...
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_Tabl e 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_FK 1 FOREIGN KEY (manager_id)
Add Constraint Branch_Table_UQ 1 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_FK 1 FOREIGN KEY (manager_id)
References Employee_Table (emp_id),
Add Constraint Branch_Table_UQ 1 Unique (manager_id)
Best, Hugo
--

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


Jul 20 '05 #3
Brian Basquille (re**********@p lease.com) writes:
The SQL i've written which doesn't seem to work is:

ALTER TABLE Branch_Table
Add Constraint Branch_Table_FK 1 FOREIGN KEY (manager_id)
Add Constraint Branch_Table_UQ 1 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_FK 1 FOREIGN KEY (manager_id)
References Employee_Table (emp_id)),
Constraint Branch_Table_UQ 1 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****@sommarsk og.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_FK 1 FOREIGN KEY (manager_id)
REFERENCES Employee_Table (emp_id),
CONSTRAINT Branch_Table_UQ 1 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
44307
by: Heist | last post by:
Hi, I just want to know to turn this: CREATE TABLE . ( NOT NULL , (50) COLLATE French_CI_AS NULL , NOT NULL , (50) COLLATE French_CI_AS NOT NULL , NULL , NULL ) ON into this:
1
5498
by: Daniel Chou | last post by:
Hello, I have two questions about "not logged initially": 1. Before using "alter table tbname activate not logged initially", should the table be created with "not logged initially"? 2. After using "alter table tbname activate not logged initially", how to deactivate it?
5
10314
by: Przemek Wrzesinski | last post by:
Hi, I'm trying to add additional column using 'alter table' command via OleDB to Excel workbook (one sheet called queExportBOND): Dim strConn As String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Temp\BOND.xls;" & _ "Extended Properties=""Excel 8.0;HDR=YES;""" Dim oCn As New OleDbConnection(strConn)
1
3450
by: ManningFan | last post by:
Does anyone know if an online list exists that has all available syntax for the Alter Table command? I need to know how to change fields to text, currency, double, etc... and change the length of text fields. Any help would be... helpful. :o)
1
2690
by: obastard | last post by:
Hi Having a problem with a ms sql 2000 server. The script below was created i SQL manager 2005 lite and gives a syntax error near '(' ALTER TABLE . ADD CONSTRAINT PRIMARY KEY CLUSTERED () WITH ( PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF,
2
17796
by: Disha | last post by:
Hi All, i wanted to know how should i alter the name of a table. Im using Alter table name Rename to new_name. But its not working kindly help me out!!!!!
2
2347
by: chandu0104 | last post by:
how to use constraints using alter command in oracle 9i(sql) i want syntax and exampes. Can i get oracle9i material in net examples for triggers,cursors in pl/sql
1
1592
by: cmartin1986 | last post by:
Hi, I am trying to write code to change my table data types. what i have so far is: currentdb.execute "alter table getdates alter column Line Unit 0001 date" If I leave it at that it works fine but when i add identical code for Line Unit 0002 below it I get an error. "syntax error in alter table statement" does anyone know what the problem is or how I can make my code change the data type in all 13 field I need it for? Thanks...
0
1611
by: kleach | last post by:
If I have a large(200gb), medium width(300 bytes) table and need to add a column, we have always unloaded data, dropped and recreated the table then loaded data again to ensure performance. What is the cost of using alter table on future queries?
0
9932
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10726
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10405
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9558
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7957
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
3
3226
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.