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 - Integrity Check - Burden on SQL

Dear Experts
While doing a Package i need to create a lot of relations for integrity
check. Am putting a burden on SQL Server for doing the hard work!
Question
Somewhere i read that it is better to do the integrity check from the front
end itself so that perfomances are not affected with many relationships in
force. What is the real style? or the right way to do? I am using MSDE 7.0
with VB
Thanks for your help
Manish Sawjiani
Three Cheers to Technet for the Help!
hi manish
Performing Integrity check from front-end is not a good practice. You will
be increasing
database hits by doing so and there by incereasing the network traffic.
This will definetely brings down the performance of your application.
Database Management Systems are designed to hold take database related loads.
If you perform integrity check in the front end then the integrity is
restricted.
i.e the check is done when you perform operations from frontend only, if
some one tries to
add data in the backend, the check is not performed and you will end up
having invalid data.
so dont wory and use Integrity check at backend itself
hope this answers your question. Is there anything that you would like to know
thanks and regards
Chandra
"Manish Sawjiani" wrote:

> Dear Experts
> While doing a Package i need to create a lot of relations for integrity
> check. Am putting a burden on SQL Server for doing the hard work!
> Question
> Somewhere i read that it is better to do the integrity check from the front
> end itself so that perfomances are not affected with many relationships in
> force. What is the real style? or the right way to do? I am using MSDE 7.0
> with VB
> Thanks for your help
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Relationship - Integrity Check - Burden on SQL

Dear Experts
While doing a Package i need to create a lot of relations for integrity
check. Am putting a burden on SQL Server for doing the hard work!
Question
Somewhere i read that it is better to do the integrity check from the front
end itself so that perfomances are not affected with many relationships in
force. What is the real style? or the right way to do? I am using MSDE 7.0
with VB
Thanks for your help
Manish Sawjiani
--
Three Cheers to Technet for the Help!hi manish
Performing Integrity check from front-end is not a good practice. You will
be increasing
database hits by doing so and there by incereasing the network traffic.
This will definetely brings down the performance of your application.
Database Management Systems are designed to hold take database related loads.
If you perform integrity check in the front end then the integrity is
restricted.
i.e the check is done when you perform operations from frontend only, if
some one tries to
add data in the backend, the check is not performed and you will end up
having invalid data.
so dont wory and use Integrity check at backend itself
hope this answers your question. Is there anything that you would like to know
thanks and regards
Chandra
"Manish Sawjiani" wrote:
> Dear Experts
> While doing a Package i need to create a lot of relations for integrity
> check. Am putting a burden on SQL Server for doing the hard work!
> Question
> Somewhere i read that it is better to do the integrity check from the front
> end itself so that perfomances are not affected with many relationships in
> force. What is the real style? or the right way to do? I am using MSDE 7.0
> with VB
> Thanks for your help
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Relationship - Integrity Check - Burden on SQL

Dear Experts
While doing a Package i need to create a lot of relations for integrity
check. Am putting a burden on SQL Server for doing the hard work!
Question
Somewhere i read that it is better to do the integrity check from the front
end itself so that perfomances are not affected with many relationships in
force. What is the real style? or the right way to do? I am using MSDE 7.0
with VB
Thanks for your help
Manish Sawjiani
Three Cheers to technet for the Help!hi manish
Performing Integrity check from front-end is not a good practice. You will
be increasing
database hits by doing so and there by incereasing the network traffic.
This will definetely brings down the performance of your application.
Database Management Systems are designed to hold take database related loads
.
If you perform integrity check in the front end then the integrity is
restricted.
i.e the check is done when you perform operations from frontend only, if
some one tries to
add data in the backend, the check is not performed and you will end up
having invalid data.
so dont wory and use Integrity check at backend itself
hope this answers your question. Is there anything that you would like to kn
ow
thanks and regards
Chandra
"Manish Sawjiani" wrote:

> Dear Experts
> While doing a Package i need to create a lot of relations for integrity
> check. Am putting a burden on SQL Server for doing the hard work!
> Question
> Somewhere i read that it is better to do the integrity check from the fron
t
> end itself so that perfomances are not affected with many relationships in
> force. What is the real style? or the right way to do? I am using MSDE 7.0
> with VB
> Thanks for your help
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!

Relations without contrains

If I created relation between 2 tables without any constarins what will be the value of this relation or when SQL Server will use this relationI'm not sure what you mean. When you create a relation there is always a constraint being created.sql

Relations without contrains

If I created relation between 2 tables without any constarins what will be the value of this relation or when SQL Server will use this relationI'm not sure what you mean. When you create a relation there is always a constraint being created.

Relations Error -

Dim verilaDataAdapter1 As SqlCeDataAdapter
Dim verilaDataAdapter2 As SqlCeDataAdapter
Dim iliski As DataRelation
Dim Sorgu1 As String
Dim Sorgu2 As String

Dim A1Dataset As DataSet
Dim parametre1 As SqlCeParameter
Dim parametre2 As SqlCeParameter
Dim ssceconn As New SqlCeConnection("Data Source =\Program Files\AtisoftDA\atisoft.sdf")
ssceconn.Open()
Dim sqlInsertRow As SqlCeCommand = ssceconn.CreateCommand()

Sorgu1 = "SELECT * FROM StokKartTbl WHERE StokGrupKodu like @.StokGrupKodu Order By Stokismi ASC;"
Sorgu2 = "SELECT * FROM SarzKartTbl WHERE SarzID like @.Sarzid"

A1Dataset = New DataSet

verilaDataAdapter1 = New SqlCeDataAdapter(Sorgu1, ssceconn) 'StokkartTbl
parametre1 = New SqlCeParameter("@.StokGrupKodu", SqlDbType.NText, 25)
parametre1.Value = Trim(Mid(CBStGrupAciklama.Text, 1, 6)) + "%"
verilaDataAdapter1.SelectCommand.Parameters.Add(parametre1)
verilaDataAdapter1.Fill(A1Dataset, "StokKartTbl")

verilaDataAdapter2 = New SqlCeDataAdapter(Sorgu2, ssceconn) 'SarzKartTbl
parametre2 = New SqlCeParameter("@.Sarzid", SqlDbType.NText, 50)
parametre2.Value = Trim(Mid(CBSarzID.Text, 1, 25)) + "%"
verilaDataAdapter2.SelectCommand.Parameters.Add(parametre2)
verilaDataAdapter2.Fill(A1Dataset, "SarzKartTbl")

Dim p As DataColumn
Dim c As DataColumn
p = A1Dataset.Tables("StokKartTbl").Columns("StokID")
c = A1Dataset.Tables("SarzKartTbl").Columns("StokID")
iliski = New DataRelation("Elemanlar", p, c)
A1Dataset.Relations.Add(iliski)

DataGridStokKartTbl.DataSource = A1Dataset.Tables("StokKartTbl")

'Error

System.ArgumentException was unhandled
Message="Hata iletisi i?eren iste?e ba?l? bir kaynak derlemesi bulunamad???ndan bir hata iletisi g?rüntülenemiyor"
StackTrace:
System.Data.ConstraintCollection.AddForeignKeyConstraint()
System.Data.ConstraintCollection.Add()
System.Data.ConstraintCollection.Add()
at DataSetRelationCollection.AddCore()
System.Data.DataRelationCollection.Add()
AtisoftDA.siparis.Button2_Click()
System.Windows.Forms.Control.OnClick()
System.Windows.Forms.Button.OnClick()
System.Windows.Forms.ButtonBase.WnProc()
System.Windows.Forms.Control._InternalWnProc()
Microsoft.AGL.Forms.EVL.EnterMainLoop()
System.Windows.Forms.Application.Run()
AtisoftDA.siparis.Main()

Hi AtiSoft,

Looks like the community did not really understand your problem or the post semantics made them to show less interest in this thread. If you are still facing the problem, I would request you to post a new query with a detailed problem description than just the program dump!

Thanks,

SQL Server Mobile/Everywhere Forum Moderator

Relations between tables - contraints diagram

Hi all,
I have a big problem. I have many tables with constraints, with foreign keys. I need to create a ordered list of tables, on the top must be the basic table what has no parents, then the second level tables (those depends on the first level) the the names of third level etc.

for example:
Table A[id]
Table B[id, idc]
Table C[id, ida]
Table D[id, ida]
Table E[id, idc]

I tried it by using information_scheme but I was unsuccesfull.

The result should be:
A
C
D
B
E

Thank you,
Tom.There isn't always a relational solution to this problem, because users sometimes create non-linear (aka graphical) relations. I know... Bad user! Naughty user! No donut!

I worked up an iterative solution, but I don't have it handy. If no one else posts a solution (you still have a copy of that one Rudy?), I'll dig it up and post it, but it might be a day or three.

-PatP|||thank Pat,
I apreciate your help, I am also working now on a iterative solution, but it does not work. So if you will have a time I will be very glad to see your solution and help, three days or five, does not matter.
Thanks again.

Tom.

Relations

Maybe a stupid question but...

is it posible to add relations in sql compact databases (.sdf) ?

I tried in server explorer (in orcas) and there is no option for relations. Also .sdf file cannot be opened in SQL Server Express Management Studio SP2 because is version 3.5.

This SQL code will do the trick:

Code Snippet

ALTERTABLE EQMetaData ADDCONSTRAINT FK_Reference FOREIGNKEY([EQID])REFERENCES Equipments ([ID])

In version 3.5 there will be a GUI for modifying relations, but for now you must use SQL code.

sql