Relatively easy SQL SELECT statement issues :/

Hi All,

I am trying to perform a relatively simple SELECT query. Firstly heres my 3 tables im working on:

CREATE TABLE business_contact
(
BusContactID INT NOT NULL AUTO_INCREMENT,
Title VARCHAR(5),
Surname VARCHAR(30),
FirstName VARCHAR(30),
PRIMARY KEY (BusContactID)
) TYPE = INNODB;

CREATE TABLE company
(
CompanyID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Manager VARCHAR(25),
PRIMARY KEY (CompanyID)
) TYPE = INNODB;

CREATE TABLE works_for
(
CompanyID INT NOT NULL,
BusContactID INT NOT NULL,
Index (CompanyID),
FOREIGN KEY (CompanyID) REFERENCES company (CompanyID) ON UPDATE CASCADE ON DELETE CASCADE,
Index (BusContactID),
FOREIGN KEY (BusContactID) REFERENCES business_contact (BusContactID) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (CompanyID, BusContactID)
) TYPE = INNODB;

The 'company' table quite obviously stores details about a range of companies, the 'business_contact' about business contacts and the 'works_for' table uses the PK values from the previous tables to associate a contact with a particular employer.

What i want to do is to retrieve a list of all the contacts and (if applicable) the name of the company they work for. My knowledge of SQL is relatively limited and so far ive managed to retrieve the all the contact and the where applicable, the key value of the company a contact works for. So all i really need to do is replace the key value with the company name, but, i dont know how!! :confused:

Heres my current query:

SELECT business_contact.*, works_for.buscontactid
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid

Can anyone help me with fetching all the contacts in the table and if the contact works for a company then listing the name of the company?!?!?

Thanks in advance to anyone who can help

Damocles.SELECT business_contact.*, company.name
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid
LEFT JOIN company
ON works_for.companyid = company.companyid|||select business_contact.Title
, business_contact.Surname
, business_contact.FirstName
, company.Name
, company.Manager
from business_contact
left outer
join works_for
on business_contact.BusContactID
= works_for.BusContactID
left outer
join company
on works_for.CompanyID
= company.CompanyID|||Thanks to both for your replies, works great!

I could really do to read a decent SQL tutorial at some point!

Damocles.

Relationsships

Trying to stablish reatlionship between tables"
BioData and EventstHistory with a key called Enumber in
BioData and foreing key called also Enumber in
EventsHistory, i get this message:
'biodata' table saved successfully
'eventshistory' table
- Unable to create
relationship 'FK_eventshistory_biodata'.
ODBC error:
SQL Server]ALTER TABLE statement conflicted with COLUMN
FOREIGN KEY constraint 'FK_eventshistory_biodata'.
The conflict occurred in database 'hrMasterData',
table 'biodata',
column 'enumber'.Fredy,
Can you please pass us the ALTER TABLE script you executed and the structure
for the two tables?
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"FREDY COREA" <fredycorea@.hotmail.com> wrote in message
news:59fa01c37700$91828020$a601280a@.phx.gbl...
> Trying to stablish reatlionship between tables"
> BioData and EventstHistory with a key called Enumber in
> BioData and foreing key called also Enumber in
> EventsHistory, i get this message:
> 'biodata' table saved successfully
> 'eventshistory' table
> - Unable to create
> relationship 'FK_eventshistory_biodata'.
> ODBC error:
> SQL Server]ALTER TABLE statement conflicted with COLUMN
> FOREIGN KEY constraint 'FK_eventshistory_biodata'.
> The conflict occurred in database 'hrMasterData',
> table 'biodata',
> column 'enumber'.|||I did not crate any script, i did it from the table Design
Table option, is there any way to gather the script
generated?
>--Original Message--
>Fredy,
>Can you please pass us the ALTER TABLE script you
executed and the structure
>for the two tables?
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"FREDY COREA" <fredycorea@.hotmail.com> wrote in message
>news:59fa01c37700$91828020$a601280a@.phx.gbl...
>> Trying to stablish reatlionship between tables"
>> BioData and EventstHistory with a key called Enumber in
>> BioData and foreing key called also Enumber in
>> EventsHistory, i get this message:
>> 'biodata' table saved successfully
>> 'eventshistory' table
>> - Unable to create
>> relationship 'FK_eventshistory_biodata'.
>> ODBC error:
>> SQL Server]ALTER TABLE statement conflicted with COLUMN
>> FOREIGN KEY constraint 'FK_eventshistory_biodata'.
>> The conflict occurred in database 'hrMasterData',
>> table 'biodata',
>> column 'enumber'.
>
>.
>|||Thanks, here its :
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.eventshistory ADD CONSTRAINT
FK_eventshistory_biodata FOREIGN KEY
(
enumber
) REFERENCES dbo.biodata
(
enumber
)
GO
COMMIT
>--Original Message--
>Fredy,
>Yes.There is a button called 'save change script' , the
third one from left.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"FREDYCOREA" <fredycorea@.hotmail.com> wrote in message
>news:586701c37703$2f03c7d0$a501280a@.phx.gbl...
>> I did not crate any script, i did it from the table
Design
>> Table option, is there any way to gather the script
>> generated?
>>
>> >--Original Message--
>> >Fredy,
>> >
>> >Can you please pass us the ALTER TABLE script you
>> executed and the structure
>> >for the two tables?
>> >
>> >--
>> >Dinesh.
>> >SQL Server FAQ at
>> >http://www.tkdinesh.com
>> >
>> >"FREDY COREA" <fredycorea@.hotmail.com> wrote in message
>> >news:59fa01c37700$91828020$a601280a@.phx.gbl...
>> >> Trying to stablish reatlionship between tables"
>> >> BioData and EventstHistory with a key called Enumber
in
>> >> BioData and foreing key called also Enumber in
>> >> EventsHistory, i get this message:
>> >>
>> >> 'biodata' table saved successfully
>> >> 'eventshistory' table
>> >> - Unable to create
>> >> relationship 'FK_eventshistory_biodata'.
>> >> ODBC error:
>> >>
>> >> SQL Server]ALTER TABLE statement conflicted with
COLUMN
>> >> FOREIGN KEY constraint 'FK_eventshistory_biodata'.
>> >>
>> >> The conflict occurred in database 'hrMasterData',
>> >> table 'biodata',
>> >> column 'enumber'.
>> >
>> >
>> >.
>> >
>
>.
>|||Fredy,
The below script works fine.
CREATE TABLE biodata
(
enumber INT PRIMARY KEY
)
GO
CREATE TABLE eventshistory
(
enumber INT
)
GO
ALTER TABLE dbo.eventshistory ADD CONSTRAINT
FK_eventshistory_biodata FOREIGN KEY
(
enumber
) REFERENCES dbo.biodata
(
enumber
)
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"fredy" <fredycorea@.hotmail.com> wrote in message
news:5a7901c37706$1b5f85e0$a601280a@.phx.gbl...
> Thanks, here its :
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> COMMIT
> BEGIN TRANSACTION
> ALTER TABLE dbo.eventshistory ADD CONSTRAINT
> FK_eventshistory_biodata FOREIGN KEY
> (
> enumber
> ) REFERENCES dbo.biodata
> (
> enumber
> )
> GO
> COMMIT
>
>
> >--Original Message--
> >Fredy,
> >
> >Yes.There is a button called 'save change script' , the
> third one from left.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"FREDYCOREA" <fredycorea@.hotmail.com> wrote in message
> >news:586701c37703$2f03c7d0$a501280a@.phx.gbl...
> >> I did not crate any script, i did it from the table
> Design
> >> Table option, is there any way to gather the script
> >> generated?
> >>
> >>
> >> >--Original Message--
> >> >Fredy,
> >> >
> >> >Can you please pass us the ALTER TABLE script you
> >> executed and the structure
> >> >for the two tables?
> >> >
> >> >--
> >> >Dinesh.
> >> >SQL Server FAQ at
> >> >http://www.tkdinesh.com
> >> >
> >> >"FREDY COREA" <fredycorea@.hotmail.com> wrote in message
> >> >news:59fa01c37700$91828020$a601280a@.phx.gbl...
> >> >> Trying to stablish reatlionship between tables"
> >> >> BioData and EventstHistory with a key called Enumber
> in
> >> >> BioData and foreing key called also Enumber in
> >> >> EventsHistory, i get this message:
> >> >>
> >> >> 'biodata' table saved successfully
> >> >> 'eventshistory' table
> >> >> - Unable to create
> >> >> relationship 'FK_eventshistory_biodata'.
> >> >> ODBC error:
> >> >>
> >> >> SQL Server]ALTER TABLE statement conflicted with
> COLUMN
> >> >> FOREIGN KEY constraint 'FK_eventshistory_biodata'.
> >> >>
> >> >> The conflict occurred in database 'hrMasterData',
> >> >> table 'biodata',
> >> >> column 'enumber'.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relationships: Cascade delete

Hi,

I Use SQL Server 2005 Express edition. I have a few tables, which are inter-related. For e.g. I have a project table (ProjectID, Name, ...)

I also have a Project invoice table, in which ProjectID is referred as Foreign key.

Now, I want all the rows in Project invoice (child) table table to be deleted (for a particular ProjetID), if I delete the coresponding ProjectID in Projects (parent) table. If I use the 'on delete set null' or 'on delete cascade' constraint, only the ProjectID (in child table) is nullified whereas rest of the columns have data.

Is there any way out, where the whole ROW (not just that field) is deleted? Or, is it that this can be achieved only by some stored procedure / external program?

Thanks!

Hi !

That is done using "On cascade delete" option. The Null option does the effect you described first, setting the foreign key field to Null. Seems that you have an old vesion of the foreign key in place. Drop the Constraint and recreate it using the "on delete cascade" keywords and you will see that the row will be deleted.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

relationships, primary key sql2000 question.

Hello,

I have 2 tables with a field called userid which is uniqueidentifier and they are both primary keys.

When I add a record to table1 and the userid field is filled, what is the best way to update table 2 with the same record.

Is there an sql function that will update automatically or do I have to write code in vb.net to select the record form table1 and insert into table2.

Thanks

Peter

If you wanted to do it relationship, one of the keys would have to be a foreign key and the other the primary key, probably using a 1 to 1 relationship.

If you want to use two primary keys, you would either need to do it by T-SQL or on the .NET Server end, ex. with VB.NET.

|||

What you are looking for is called a DRI(declarative referential integrity) constraint. You create it with the enable relationship dialog box at the top of Management Studio and look for option Cascade on Update. The two links below one explains DRI and the second is a walkthrough to enable it. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177288.aspx

http://msdn2.microsoft.com/en-us/library/ms186973.aspx

|||

Thanks guys, working through links now.

Peter

sql

Relationships vs. Joins

Hi,

I was wondering when designing a database with multiple tables with PKs and FKs in place, why would I think to map those FKs and PKs between tables hence making relationships while I can retrieve whatever I need with joins without creating any relationships?

Hi loopool.

There's lots of reasons you'd want to consider this, here's a couple:

1) Data integrity - constraints (pk's, fk's, checks, defaults, unique, etc.) are primarily used to enforce data integrity within your database (i.e. domain, entity, referential). They enforce things like ensuring valid values exist for a given parent/child type record, ranges of valid data, duplicates, etc. FK's can also ensure deletes don't allow 'orphaned' records in your system through cascading actions. See the following topic in books online for a brief discussion of data integrity:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/475233a9-b46f-4aa8-aa13-d388beb9f069.htm

2) Read optimization - the query optimizer can make use of many of these types of constraints for use with optimizing query plans in a variety of different ways - one of which can be when you are using joins for example as you mentioned above.

There's a variety of other reasons for using them, but that's a start...HTH,

Relationships problem

This may be the same problem as the "left outer join" thread below and i apologise in advance.
I have two tables : Costings and Purchase names.
Costings has the following fields:- Value, Supplier code.
Purchase names has the following fields: Supplier code, supplier name.
I have linked the two tables together.
The problem I have is that the supplier code can be blank in costings due to adjustments and the report doesnt print it out because it cant find a supplier name in the Purchase names table.
Any ideas as to how to show the entries with blanks codes?
(I have read the previous thread and think the answers might related).
Thanks in advance,
SimonIn your report go to DATABASE Menu -> VISUAL LINKING EXPERT

select link between yr 2 table and then click on LINK OPTIONS button and if your Cost Table is left hand side then give Left Outer Join and if it is right hand side then Give Right Outer join...|||hi jmendpara,
Will give it ago, thanks for the advice,
Simon|||thanks that worked:)

Relationships on MSDE

I am an Access user.
In Access when I create a relationship between 2 tables is use
Enforce Referential Integrity
Cascade Update Related Fields
Cascade Delete Related Records
If I change data in Primary Table is Update automaticaly in Foreign table.

I use a MSDE database, and I create 2 tabele
Table 1 - with a primary key (AUT_ID)
Table 2 (Foreign table) with 2 fields
Field :AUT_ID
Field: Field1
I use Access interfaces (adp Database) and I create a Diagram
Primary key table: Table_1; Field: AUT_ID
Foreign table: Table_2; Field: AUT_ID

PROBLEM: If I change data in Table_1 (field AUT_ID) data is not change in Table_2 and error occurs.[i]
PROBLEM: If I change data in Table_1 (field AUT_ID) data is not change in Table_2 and error occurs.

I see nothing that requests any sort of cascading-update.

Furthermore, Table_2 is clearly the master-table of the relationship and Table_1 the subordinate. Thus a change to Table_1 to introduce a key not in Table_2 would be disallowed, as you see.|||Is a version problem if I understud corectly
SQL vers.7 did not suport ON UPDATE NO ACTION / CASCADE
SQL vers.8 (suport ON UPDATE NO ACTION / CASCADE)
I installed vers.8 and everything is OK.

Thanks.

Relationships in Management Studio Express connected to SQL Server Compact Edition

Hello ,
Is possible to specify reationships between tables on SQL Server Compact Edition database in Management Studio Express ?

I can connect to database file , add tables, add data , but I dont't found the right methot to specify reationships as with SQL Server Express.

Any ideas?

Have you found an answer?

Gus

|||

You have to specify the relationships in the query analyzer, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)|||Thanks Erik for the information. I know it can be done in code, I was hoping to find the complete table designer in the ce edition. I understand that it's not there. Apparently 'lightweight' refers not just to the product's functional capabilities, but also its development tools.|||Designing relationships visually will be available in the next version of SQL CE, version 3.5, included in Visual Studio "Orcas".sql

Relationships in Management Studio Express connected to SQL Server Compact Edition

Hello ,
Is possible to specify reationships between tables on SQL Server Compact Edition database in Management Studio Express ?

I can connect to database file , add tables, add data , but I dont't found the right methot to specify reationships as with SQL Server Express.

Any ideas?

Have you found an answer?

Gus

|||

You have to specify the relationships in the query analyzer, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)|||Thanks Erik for the information. I know it can be done in code, I was hoping to find the complete table designer in the ce edition. I understand that it's not there. Apparently 'lightweight' refers not just to the product's functional capabilities, but also its development tools.|||Designing relationships visually will be available in the next version of SQL CE, version 3.5, included in Visual Studio "Orcas".

Relationships between tables in different SQL databases

There are several databases (currently in Access) that are being moved to SQL Server. Would prefer keeping those databases separate in SQL Server. How do you do something similar to Access's "link" capabilities and relate tables in different physical databases? For example, relate the authors of a document in a document database to persons in a people database where AuthorID in Docs.DocAuthors.AuthorID is related to People.Persons.PersonID

It is normal in SQL Server to combine all of the data for an application into a single database.

SQL Server does not have the size limitations of Access.

You can keep security separate (if that is your need) with the use of Schemas. Refer to Books Online, Topics:

Schema

User-Schema Separation

|||

Found out the SQL doesn't support cross-database foreign keys. Using schemas to separate related tables requires extra typing when programming and requires tedious program updates if a table changes schema. Will stick to keeping all tables in one database and under the default "dbo" schema.

Not sure if SQL has a similar "dbLink" API similar to Oracle's "dbLink" to SQL. Still looking for equivalent "dbLink" in SQL.

|||Refer to Books Online, Topic: 'Linked Servers'

relationships as a table

I have the tables tblEmployees and tblProjects.
tblProjects has one column called Percentagetime, to hold the percentage of
time by an employee for a project.

My Question : Why should we introduce a 3rd table called
tblProjectsAndEmployees?
tblProjectsAndEmployees will have
1)EmployeeNumber(PrimaryKey in tblEmployees)
2)ProjectNumber(Primary key in tblProjects)
3)PercentageTime

Having only the tblEmployees and tblProjects is not sufficient?

Kind Regards,
Vinodh Kumar PVinodh Kumar P (vinodh@.here.com) writes:
> I have the tables tblEmployees and tblProjects. tblProjects has one
> column called Percentagetime, to hold the percentage of time by an
> employee for a project.
> My Question : Why should we introduce a 3rd table called
> tblProjectsAndEmployees?
> tblProjectsAndEmployees will have
> 1)EmployeeNumber(PrimaryKey in tblEmployees)
> 2)ProjectNumber(Primary key in tblProjects)
> 3)PercentageTime
> Having only the tblEmployees and tblProjects is not sufficient?

As long as an employee only works on one single project it is. Or a project
never can be staffed by more than one employee.

But in real life an employee works on more than project, and project have
several members. Then you have a many-to-many relation, and the typical
way to sort that out is to introduce a linking table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Relationships - how would you do this?

Hi there,
I have a database design problem and I don't know how to go about this. Any
help would be greatly welcome!
I've simplified my tables for the sake of understanding. I've created a
fictional scenario that is exactly the same as my more complex one. My
example involves the selling of three unique items - cars, boats and planes.
Each item has it's own table that is distincly different than the other two
items (eg - car fields are totally different than boat or plane fields). My
tables are as follows:
tbl_Inventory - Simply contains a list of Cars, Boats and Planes to be sold.
tbl_Cars - A detailed list of all of the cars for sale
tbl_Boats - A detailed list of all of the boats for sale
tbl_Planes - A detailed list of all the planes for sale
Now, how would you go about linking these tables together. The way I did it
was by using the following two fields in my tbl_Inventory table:
Ref_ID - The ID of the table we are referencing
Ref_Type - The actual type of table we are referencing (1 is car, 2 is boat,
3 is plane)
Eg - Ref_ID = 6, Ref_Type = 2 would mean that in the boat table we are
looking at boat ID 6.
This seems okay but how do you create a relationship for this type of layout
that would, for example, allow for cascade deleting of a record in the
tbl_Cars table to delete the associated car record in the tbl_Inventory
table?
I've tried adding a primary key to tbl_Inventory to both the Ref_ID and
Ref_Type fields but then how do you generate a relationship? Am I doing
something wrong?
Any help would be appreciated.
Chris.(Irritating Access-style tbl_ prefixes ignored. We already know these are
tables!)
CREATE TABLE Inventory (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL CHECK
(type IN ('C','B','P')) /* Car/Boat/Plane */, UNIQUE (type,ref), /* Common
columns such as description, price, quantity, etc in this table */)
CREATE TABLE Cars (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
'C', CHECK (type='C'), FOREIGN KEY (type,ref) REFERENCES Inventory
(type,ref) ON DELETE CASCADE)
CREATE TABLE Boats (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
'B', CHECK (type='B'), FOREIGN KEY (type,ref) REFERENCES Inventory
(type,ref) ON DELETE CASCADE)
CREATE TABLE Planes (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
'P', CHECK (type='P'), FOREIGN KEY (type,ref) REFERENCES Inventory
(type,ref) ON DELETE CASCADE)
David Portas
SQL Server MVP
--|||This is great. Thanks a tone David.
Chris.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:cIqdnSHZq-FJlWncRVn-2w@.giganews.com...
> (Irritating Access-style tbl_ prefixes ignored. We already know these are
> tables!)
> CREATE TABLE Inventory (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL
> CHECK (type IN ('C','B','P')) /* Car/Boat/Plane */, UNIQUE (type,ref), /*
> Common columns such as description, price, quantity, etc in this table */)
> CREATE TABLE Cars (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
> 'C', CHECK (type='C'), FOREIGN KEY (type,ref) REFERENCES Inventory
> (type,ref) ON DELETE CASCADE)
> CREATE TABLE Boats (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL DEFAULT
> 'B', CHECK (type='B'), FOREIGN KEY (type,ref) REFERENCES Inventory
> (type,ref) ON DELETE CASCADE)
> CREATE TABLE Planes (ref INTEGER PRIMARY KEY, type CHAR(1) NOT NULL
> DEFAULT 'P', CHECK (type='P'), FOREIGN KEY (type,ref) REFERENCES Inventory
> (type,ref) ON DELETE CASCADE)
> --
> David Portas
> SQL Server MVP
> --
>|||I would not do it that way, I would setup a properties table that held the
unique attributes between the boats, planes and cars. This would allow you
to use the same code for all types of products. If you start selling
motorcycles you will have to rewrite your code.
Jim
"ChrisN" <cnewald@.hotmail.com> wrote in message
news:#f3dHgYAFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I have a database design problem and I don't know how to go about this.
Any
> help would be greatly welcome!
> I've simplified my tables for the sake of understanding. I've created a
> fictional scenario that is exactly the same as my more complex one. My
> example involves the selling of three unique items - cars, boats and
planes.
> Each item has it's own table that is distincly different than the other
two
> items (eg - car fields are totally different than boat or plane fields).
My
> tables are as follows:
> tbl_Inventory - Simply contains a list of Cars, Boats and Planes to be
sold.
> tbl_Cars - A detailed list of all of the cars for sale
> tbl_Boats - A detailed list of all of the boats for sale
> tbl_Planes - A detailed list of all the planes for sale
> Now, how would you go about linking these tables together. The way I did
it
> was by using the following two fields in my tbl_Inventory table:
> Ref_ID - The ID of the table we are referencing
> Ref_Type - The actual type of table we are referencing (1 is car, 2 is
boat,
> 3 is plane)
> Eg - Ref_ID = 6, Ref_Type = 2 would mean that in the boat table we are
> looking at boat ID 6.
> This seems okay but how do you create a relationship for this type of
layout
> that would, for example, allow for cascade deleting of a record in the
> tbl_Cars table to delete the associated car record in the tbl_Inventory
> table?
> I've tried adding a primary key to tbl_Inventory to both the Ref_ID and
> Ref_Type fields but then how do you generate a relationship? Am I doing
> something wrong?
> Any help would be appreciated.
> Chris.
>sql

Relationship problem

Hi All... Two of my tables are:

Users - primary key is UserId, an int with identity turned on.

Messages - has a column named UserId that references the same in Users.

I'm using Visual Studio 2005 against a SQL 2005 database.

Using both the diagram tool and table data, I'm trying to set up the relationship implied above and am getting the following error:

Users table saved successfully.

Messages table

- unable to create relationship 'FK_Messages_UserId'.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 'FK_Messages_UserId'. The conflict occurred in database 'XXXX', table 'dbo.Users', column 'UserId'.

I've done several other similar relationships without incident. But this one (and one or two others) refuse to work. I'm a bit of newbie with these rascals, so that doesnt help much... Any ideas what this things trying to tell me? Thanks! -- Curt

You might already have entries in your child table which have no parent entry in the parent table.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens... Thanks for the reply. You threw me a little at first on your use of "parent" and "child", but yeah you nailed it. To try and tie those terms to the tables in my original post, I had some records in Messages (child) that referenced a primary key that did not exist in Users (parent). Geez, these FKs really help us keep a clean house, dont they... Thanks again!! Curt

Relationship in Access, what about SQL Server 2000?

Hello,

In Microsoft Access, there is a feature to create relationships between tables. When creating these relations, you have the option to:

1. Establish the relation type. For example, 1 to many (primary key to foreign key)

2. Enforce referential integrity

3. Allow cascading updates

4. Allow cascading deletions

5. View these relations in a nice diagram

Does SQL Server 2000 contain this functionality? If so, where is this found in the Enterprise Manager? If not, what is the alternative to this very useful feature found in Access?

Thanks You For Any Help!

In Enterprise Manager select your Db, select Diagrams (Create One if neccessary - use auto) and then use drag and drop with the key fields to create the relationships. You will get a nice popup with various options such as cascading etc.|||

You can also use the design view or sql command to do this.

If you want to use the design view to add relationship, do the followings

Open EM|||

HIdefyant_2004

Sql server gives to us many features and there are many ways to create relationship between two tables. Here is the common way to create

1. Open EM

2. Right_click on Diagrams

3. Select New Database Diagram...

4. Next

5. Select the tables you want to create relationship

6. Next step...like Access

Cheers!

Relationship Diagrams

Hello,
I was wondering if there is a tool available that maps the
relationships between tables in a SQL Server database.
I need this to help document a user guide to a DB I've designed for
them and they are familair with the MS Access diagrams and can't get
on with the diagrams Enterprise Manager produces.
However I want the tool to "attach" the relations to the Primary Key
and Foreign Keys regardless of where I drag the tables around, an
awful lot like MS Access relationship diagrams do. I've trawled
through many "trails" offered on the internet and thought I'd be
better off "asking the audience" for any recommendations.
Thanks for any help
Giles
If you want to document your database, you might check out SqlSpec, a
shareware app I wrote: http://www.elsasoft.org
Giles wrote:
>Hello,
>I was wondering if there is a tool available that maps the
>relationships between tables in a SQL Server database.
>I need this to help document a user guide to a DB I've designed for
>them and they are familair with the MS Access diagrams and can't get
>on with the diagrams Enterprise Manager produces.
>However I want the tool to "attach" the relations to the Primary Key
>and Foreign Keys regardless of where I drag the tables around, an
>awful lot like MS Access relationship diagrams do. I've trawled
>through many "trails" offered on the internet and thought I'd be
>better off "asking the audience" for any recommendations.
>Thanks for any help
>Giles
|||On 17 May, 21:29, "jezemine" <u34287@.uwe> wrote:
> If you want to document your database, you might check out SqlSpec, a
>
> shareware app I wrote:http://www.elsasoft.org
> Giles wrote:
>
>
> - Show quoted text -
Thanks for the tip, pretty looking app, but unfortunatley it still
doesn't map the relationships as I require.
Ta
Giles
sql

Relationship constraints

Hi,

I am trying to get the best solution for my problem:

I have 2 tables. Customer and Transaction. A transaction can contain a customer but not always. As I see it I would have 3 options in doing this.

1. Create a CustomerId field in the Transaction table and create relationship on the field but not enforced it and allow NULL or 0 for non customer related transactions.

2. Same setup as 1 but load a "dummy" customer and setup the constraints correctly. But I have a problem that I already have data in my customer table, so I can't take the easy route and only select the 1st customer as my "dummy" customer.

3. Create a CustomerTransaction table with TransactionId and CustomerId and create proper relationships.

What would be the best practice handeling the above scenario?

Regards,

Adriaan

Hi Adrian,

Remember that a foreign key constraint can ban be built upon a nullable column, so if you had migrated your CustomerID column from table Customer to table Transaction, and created it as nullable, then this would meet your needs (your option 1)

Do not go down the path of Option 2 as it's always ugly...

Option 3 will still leave you with a table identifying that a particular transaction did not involve a customer (a null CustomerID in the CustomerTransaction table) - this is no different then option 1 but introduces additional entities.

You've posed a design question, so I think you'll find that there is no "correct" answer and my idea of best practice will not be some elses ;)

Cheers,

Rob

|||Hi Robert,

Thanks for the reply I never realized that foreign key constraints can be built upon nullable columns, I just assumed that it would not be possible.

I always like to have a simple table structure and try not to introduce extra entities where I should not.

Thanks again!

Relationship between two tables

Hi,
We can create more than one
relationship between two tables. Could you tell me a situation where we nee
d two relationships between two tables?
ThanksSay you have a customers table and a table that represents a transaction bet
ween two customers holding two columns with a customer id. The referencing t
able would need to have two foreign keys pointing on the customerid
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message news:OM$bER0VFHA
.1404@.TK2MSFTNGP09.phx.gbl...
Hi,
We can create more than one
relationship between two tables. Could you tell me a situation where we nee
d two relationships between two tables?
Thanks|||Hi
You can have that kind of relationship but it violates 2nd and 3rd Normal
Forms:
For Eg:
Table_Student
Name Subject-ID Room-Number
Table_Class Room
Subject-ID Subject-Desc Room-Number
was this your question?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"§Chrissi§" wrote:

>|||> You can have that kind of relationship but it violates 2nd and 3rd Normal
Not necessarily. Suppose we have the following
Create Table Employees
(
Id Int Primary Key
, LastName VarChar(25)
, FirstName VarChar(25)
)
Create Table ManagerStaff
(
Id Int Primary Key
, ManagerId Int References Employee(Id)
, SubordinateId Int References Employee(Id)
, StartDate DateTime
, EndDate DateTime
)
I have two relationships to the Employees table yes this is still normalized
to
third normal form.
Thomas

Relationship between tables of two database

Hi All'

I'm in process of upgrading our Microsoft Access Database to SQL Server 2000. We have one front end database that links all the backend databases. But there are some databases which shares tables with other databases. Currently the refrential integrity is being done by VBA codes in the forms itself (bad na!).

Example
----
Database: Vehicle
Tables in "Vehicle" database are VechileType (v_type, v_desc) and VehicleInventory (v_RegNo, v_Type, customerID)

Database: Customer
Tables in "Customer" database are CustomerType(c_type, c_desc) and CustomerInventory (customerID, customerName, c_type).

This is just example...there are many (more than 10!) tables in each database. So, I do NOT want to place everything in a single database.

Now I'm looking solution for creating trigger that ensures the Refrential Integrity on "customerID" field in both VehicleInventory and CustomerInventory tables. eg user can not delete customerID from CustomerInventory table if its record exist in VehicleInventory table.

CAN ANY ONE HELP ME..................

Thanks

cheers'

-aviAvinash, why don't you create primary key and foreign key constraints to implement referential integrity. That's a better approach than triggers.

gyan.

Originally posted by avishesh
Hi All'

I'm in process of upgrading our Microsoft Access Database to SQL Server 2000. We have one front end database that links all the backend databases. But there are some databases which shares tables with other databases. Currently the refrential integrity is being done by VBA codes in the forms itself (bad na!).

Example
----
Database: Vehicle
Tables in "Vehicle" database are VechileType (v_type, v_desc) and VehicleInventory (v_RegNo, v_Type, customerID)

Database: Customer
Tables in "Customer" database are CustomerType(c_type, c_desc) and CustomerInventory (customerID, customerName, c_type).

This is just example...there are many (more than 10!) tables in each database. So, I do NOT want to place everything in a single database.

Now I'm looking solution for creating trigger that ensures the Refrential Integrity on "customerID" field in both VehicleInventory and CustomerInventory tables. eg user can not delete customerID from CustomerInventory table if its record exist in VehicleInventory table.

CAN ANY ONE HELP ME..................

Thanks

cheers'

-avi :)

Relationship between tables in different databases

We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.
Not possible to create foreign key constraints across databases. You will
have to use triggers to enforce this yourself.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.
|||"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> We also have a "Reference" database that is used by all of the
> applications. My manager would like me to create some relationships
> between tables in Reference and one of the other databases.
In my experience I've found this is generally a bad idea. I don't even
really think this is the intent of the technology anyway. This also
invariably means that at some point you'll have cross-database ownership
chaining in addition to other strange hack such as creating custom triggers
to enforce constraints (as someone else has stated). Also, you'll find
yourself writing a lot more dynamic SQL to get anything done.
sql

Relationship between tables in different databases

We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.Not possible to create foreign key constraints across databases. You will
have to use triggers to enforce this yourself.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
We have several applications, each with its own database. We also have a
"Reference" database that is used by all of the applications. My manager
would like me to create some relationships between tables in Reference and
one of the other databases. I can't do this in either the Database Diagram
or in Table Designer, because both only show me tables in one database at a
time. Is there another way to do it?
Much obliged.|||"Geoff" <Geoff.Pennington.ctr@.NOSPAMwhs.mil> wrote in message
news:OTsKLMYfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> We also have a "Reference" database that is used by all of the
> applications. My manager would like me to create some relationships
> between tables in Reference and one of the other databases.
In my experience I've found this is generally a bad idea. I don't even
really think this is the intent of the technology anyway. This also
invariably means that at some point you'll have cross-database ownership
chaining in addition to other strange hack such as creating custom triggers
to enforce constraints (as someone else has stated). Also, you'll find
yourself writing a lot more dynamic SQL to get anything done.