MS SQL Server :: Creating Relational Databases(Primary/Foreign Key?)

SQL XML :: Shred To Relational Tables - Creating Foreign Keys

Oct 7, 2015

I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture. The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. without taking the xml down to the tables?The problem with this approach is I have xml stored in most tables and the tables are becoming very large.

<Route Type="OneWay" >
<Seat Type="FirstClass">
<Prices>
<Price Price="10" />
<Price Price="11" />
</Prices>

[code]....

View 4 Replies View Related

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

Apr 11, 2006

Hello again,

I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:

Code:

CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

class_title
VARCHAR(50)
NOT NULL,

class_grade
SMALLINT
NOT NULL
DEFAULT 6,

class_tardies
SMALLINT
NOT NULL
DEFAULT 0,

class_absences
SMALLINT
NOT NULL
DEFAULT 0,

CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)

This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:

Code:

CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

grade_id
INT
NOT NULL,

CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),

CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)

Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?

In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?

Thank you for your assistance.

View 1 Replies View Related

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

Jul 16, 2014

what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:

ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])

What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

View 4 Replies View Related

Relational Databases

Oct 3, 2007

I have a membership database and a profile database with a userid column in both. The foreign key is in the profile database where i want a persons profile details to go like location,occupation and what not. How do I make it so when a new user registers a userId is created in the profile database that matched the userid in the membership database so i can query out profile details based on a registered members userid?

View 6 Replies View Related

Relational Databases

Jun 18, 2001

what is the difference in making relational databases in SQL vs. Access? Do you need to make them if you will be using ColdFusion? What do the other fields in minor tables need to be in order to connect them to a major table when it will all be based on a number that is in the major table? i'm kind of confused on this issue...Thanks!

View 1 Replies View Related

Relational Databases

Sep 4, 2006

Hi
I am developing a website current and i have a database that seems to be growing very rapidly..
I would like to ask if anyone knows of any good tutorials/examples on relational databases?
Also does anyone know of any good database design tutorials/examples?

If it helps i am using Microsoft Visual Web Developer

Thanks

If you think you know it all?! You know nothing!

View 3 Replies View Related

Insert Into Relational Table Primary Key From Parent

Sep 22, 2007

Hello, I have a Stored Procedure which insterts into Orders table. Orders table is the parent table, with primary key OrdersID. I also have a child table, Client, with foreign key OrdersID. I want it to insert the data into the orders table, and at the same time insert the OrdersID into the FK of the child table. Any info would be appreciated. I have no idea how to do it.
My SP is as follows:ALTER PROCEDURE dbo.jobInsert
@ClientFileNumber varchar(50),@Identity int OUT

ASINSERT Orders(ClientFileNumber, DateTimeReceived) VALUES(@ClientFileNumber, GetDate())
SET @Identity = SCOPE_IDENTITY()

RETURN

View 4 Replies View Related

Foreign And Primary Key Question

Apr 24, 2007

OK - I have a two tables in a database. Table one contains an ID, 'oneID', field as the primary key. It is auto-incremented. Table two has ID field, 'twoID', as the primary key. This field also auto-increments. Table two also has 'oneID' as the foreign key.
Now, my question is, how do I get the foreign key in table two to auto-increment in conjuction with table one's primary key? They are after all the same data. Do I have to manually code to get table one data and save it to table two data?
thanks

View 1 Replies View Related

Clustered Primary Key And Foreign Key: T-SQL

Jun 10, 2005

I am having a little trouble getting this to work right, but have come a ways since I started this.......other tables created first and with no problems..... then these two with the last table being the problemI need to set one foreign key in the second table referencing the first table.But, the primary key is clustered with the two foreign keys and I get the error....There are no primary or candidate keys in the referenced table 'courseScores' that match the referencing column list in the foreign key 'FK_course'.CREATE TABLE dbo.courseScores (     courseId varchar(20) NOT NULL    CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)    REFERENCES courseStructure (courseId),
    studentId varchar(20) NOT NULL    CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)    REFERENCES students (studentId),
    CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)
          )CREATE TABLE dbo.objScores ( tmp int IDENTITY(1,1) PRIMARY KEY,    objective varchar(50) NOT NULL,        courseId varchar(20) NOT NULL    CONSTRAINT FK_course FOREIGN KEY (courseId)    REFERENCES courseScores (courseId)                  )
Once I get it working, then the tmp will be gone and then set 3 foreign keys as the clustered primary, fyi.Not sure how to reference half a primary key?Any help is greatly appreciated.....Thanks all,Zath

View 4 Replies View Related

Can A Column Act As Both Primary Key And Foreign Key?

Sep 11, 2011

Can a column act as both primary key and foreign key, if so what is script.

And can a table have more than one primary keys/foreign keys, if so what is the script.

View 4 Replies View Related

Foreign Key Value Has No Matching Primary Key

Sep 15, 2014

I'm currently using Oracle and receiving this error:

SQL Error: ORA-02291: integrity constraint (43437001.ATHLETE_FK1) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

When inserting this value:

INSERT INTO ATHLETE VALUES ('A001','TOM','HANKS','03-FEB-90','USA','C001');

With Tables:

CREATE TABLE ATHLETE(
ATHLETEID CHAR(4),
ATHLETEFIRSTNAME VARCHAR2(20),
ATHLETELASTNAME VARCHAR(20),
ATHLETEDOB DATE,
REPCOUNTRY VARCHAR2(12),

[Code] ......

I think the error is to do with the Athletes table (primary/foreign key)

How would I fix this in order to insert values without error?

View 3 Replies View Related

Foreign And Primary Keys

Oct 19, 2005

I have an application in which i need to get the foreign key fieldsfrom a table and then get all the foreign keys primary key field fromthe linking table. Could some one tell me how i do this usingINFORMATION_SCHEMA. I have tried and can get the foreign keys but notsure how to get the associated primary keys.

View 1 Replies View Related

Composite Primary Key And Foreign Key

Feb 8, 2008

Hi,
In my table1, I have a composite primary key ssn+firstname+lastname.
In table2 I have ssn, firstname, lastname, col1. In table2 I want to create a foreign key which references table1 primary key?
table1 and table2 has the primary, foreign key relationships. Still table2 need primary key. In table2 Can I make the ssn+Firstname+Lastname as primary key? or one column id as auto increment?

Which is best way?

Thanks

View 27 Replies View Related

Primary Key/foreign Key Relationship

May 11, 2007

I'm using MS SQL Server Studio Express 9.0. When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem. But how do I create a relationship with another table (foreign key)?

Bob K.

View 11 Replies View Related

Getting Information Dependant Upon Primary && Foreign Key

Aug 11, 2006

Hi All,
It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!

I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.

The Problem?

Tables:

1.tblRisk: Ref(pk), subject, status(fk), staff(fk), Dept(fk)

2.tblDept: Ref(pk), Department

The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.

Many Thanks,
Kunal

CREATE PROCEDURE dbo.ShowMe @yourInputValue INTAS SELECT tblDept.Department FROM tblDept JOIN tblRisk ON tblDept.Ref = tblRisk.Dept WHERE tblDept.Ref = @yourInputValue RETURN 0GO

View 1 Replies View Related

Foreign Key Constraint To A Composite Primary Key

Mar 6, 2008

I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?

View 10 Replies View Related

Delete Data Which Is A Primary And Foreign Key

May 26, 2008

Hi,
How do I delete data which is a Foreign Key in another table?
For example;
string query = "DELETE * from user_details WHERE user_ID = '" +userID.Text+ "';
The user_ID is the Primary Key in the user_details table and also a Foreign Key in other tables.
Thank you. (:

View 4 Replies View Related

Dropping Primary And Foreign Keys

Jun 22, 2001

I am having trouble dropping constraints(Primary and Foreign Keys). I would like to do so so I can truncate the tables and repopulate them. Any time I use the DROP CONSTRAINT #### on one table, I get an error message saying this is referenced in another table. Any help in how to drop the keys so I can truncate the tables in a database would be appreciated. I must be overlooking something simple. Thanks for the help.

Regards,
Mark

View 2 Replies View Related

Composite Primary Key And Foreign Key - Relations

Sep 19, 2000

Hi,

I have a doubt, can anyone please clarify me.

I have created the following table,

create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It's giving error,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno),
deptno char(2)
)
go

Error:-
*****
There are no primary or candidate keys in the referenced table 'z' that match the referencing column list in the foreign key 'FK__z_dup__eno__18CE1BA6'.Could not create constraint. See previous errors.

Even I tried to create a foreign key like the following,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno),
deptno char(2)
)
go

Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table 'z_dup'.

Can anyone please help me to create a foreign key relation with the base table.

tks in advance,
Srinivasan

View 5 Replies View Related

Primary Key And Foreign Key Relation..URGENT!!!

Mar 11, 1999

Hi,

I have two tables.
CREATE TABLE Service(
SERVId varchar (10) NOT NULL,
SERVName varchar (30) NOT NULL,
SERVDesc TEXT NULL,
PRIMARY KEY(SERVId)
)

CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(SERVId,NDId)
FOREIGN KEY(SERVId)
REFERENCES Service(SERVId)
)

Msg 547, Level 16, State 2
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__Newdev__SERVId__4939E6D2'. The conflict occurred in database 'test', table 'New', column 'SERVId'
Command has been aborted.

The same problem occurs even if the second table i.e. new is declared as follows, i.e created with only one primary key:
CREATE TABLE New(
SERVId varchar (10) NOT NULL,
NDId varchar (10) NOT NULL,
NDName varchar (30) NOT NULL,
NDDesc text NULL,
PRIMARY KEY(NDId)
FOREIGN KEY(SERVId)
REFERENCES Service(SERVId)
)

what might be the problem? But the same relation works on MS Access.
Is there anyother way of declaring this kind of relationship i.e. primary and foreign key?Can anyone pl. help me in solving this problem of foreign key relation.

thanks in advance
-chaitra

View 2 Replies View Related

Create Foreign Key Using Composite Primary Key

Jul 24, 2014

I am trying to create a FK using a composite PK and here are the details that I want to achieve.

Table -A
Column A1 not null,
Column A2 not null
Primary key (A1, A2).

Table -B
Column B1 Primary key.
Column B2 not null
FK (B2) References A(A1).

When I try to do this I am getting some errors. Questions: First of all is this possible? if yes, then how I can create it.

View 1 Replies View Related

Return Primary Key Even When Foreign Key Is Empty

Feb 20, 2015

I have a query that returns contacts connected to a client, but the problem is that it's only returning a result when a contact is associated with a client, even if the client does exist in the db. i want it to still return the client if the client exists.

SELECT * FROM clients, addressbook where clients.clientid = addressbook.clientid AND (clients.clientname LIKE '" . strtoupper($_GET['txtfname'])."%')

View 1 Replies View Related

Scripting Primary & Foreign Keys Only

Oct 28, 2004

Be warned, index padding is not included in this, and I'm not sure the fillfactor setting is correct.

set nocount on
create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK primary key clustered(constraint_schema, constraint_name))
create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key clustered(constraint_schema, constraint_name, ordinal_position))
create table #FK(constraint_schema sysname not null, constraint_name sysname not null,
unique_constraint_schema sysname not null, unique_constraint_name sysname not null,
sql varchar(4000) not null, constraint PK_#FK primary key clustered(constraint_schema, constraint_name))

insert into #PK
select constraint_schema, constraint_name, 'ALTER TABLE ' + quotename(table_schema) + '.' + quotename(TABLE_NAME) +
' ADD CONSTRAINT ' + quotename(CONSTRAINT_NAME) +
' PRIMARY KEY ' + CASE WHEN si.indid<>1 THEN 'NON' ELSE '' END +
'CLUSTERED (>cols<) WITH FILLFACTOR=' + cast(si.OrigFillFactor as varchar) + ' ON ' + quotename(fg.groupname)
AS SQL
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN sysindexes si on TC.CONSTRAINT_NAME=si.name
inner join sysfilegroups fg on si.groupid=fg.groupid
WHERE CONSTRAINT_TYPE IN('PRIMARY KEY','UNIQUE')

insert into #fk
select c.constraint_schema, c.constraint_name, c.unique_constraint_schema, c.unique_constraint_name,
'ALTER TABLE ' + quotename(F.table_schema) + '.' + quotename(F.table_name) +
' ADD CONSTRAINT ' + quotename(F.constraint_name) +
' FOREIGN KEY(>cols<) REFERENCES ' + quotename(r.table_schema) + '.' + quotename(r.table_name) +
'(>rcols<)'
AS sql
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS F
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ON F.constraint_schema=C.constraint_schema AND f.constraint_name=c.constraint_name AND F.constraint_type='FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS R ON R.constraint_schema=C.unique_constraint_schema AND r.constraint_name=c.unique_constraint_name AND r.constraint_type in ('PRIMARY KEY','UNIQUE')
ORDER BY F.table_name, r.table_name

insert into #cols
select constraint_schema, constraint_name, COLUMN_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

declare @ctr int, @max int, @delim varchar(1)
select @ctr=1, @max=max(ordinal_position), @delim='' from #cols

set nocount on
while @ctr<=@max
BEGIN

update P SET SQL=Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<')
FROM #PK P INNER JOIN #cols C ON P.constraint_schema=C.constraint_schema AND P.constraint_name=C.constraint_name
WHERE C.ORDINAL_POSITION=@ctr

UPDATE F SET SQL=Replace(Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<'), '>rcols<', @delim + quotename(r.column_name) + '>rcols<')
FROM #FK F INNER JOIN #cols C ON F.constraint_schema=C.constraint_schema AND F.constraint_name=C.constraint_name AND C.ordinal_position=@ctr
INNER JOIN #cols R ON F.unique_constraint_schema=R.constraint_schema AND F.unique_constraint_name=R.constraint_name AND C.ordinal_position=R.ordinal_position

select @ctr=@ctr+1, @delim=','
END
set nocount on

update #PK SET SQL=Replace(SQL, '>cols<', '')
update #FK SET SQL=Replace(Replace(SQL, '>cols<', ''), '>rcols<', '')

select sql from #PK
select sql from #FK

drop table #pk
drop table #fk
drop table #cols

View 5 Replies View Related

Can Only Use Primary Key And Not Foreign Key While Joining Tables?

Oct 9, 2015

I was trying a joining example provided in my book in which customer is a table and person is another table. The query provided in the book is this... USE AdventureWorks2012;

GO
SELECT c.CustomerID, c.PersonID, p.BusinessEntityID, p.LastName
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID;

This is the query that I did....

SELECT
      c.CustomerID,p.FirstName,p.MiddleName,p.LastName
FROM
      Sales.Customer AS c INNER JOIN Person.Person AS p
ON
      c.CustomerID=p.BusinessEntityID

ORDER BY
         p.BusinessEntityID;

Keys :-

Person Table
[PK_Person_BusinessEntityID]
[FK_Person_BusinessEntity_BusinessEntityID]

Customer Table
[PK_Customer_CustomerID]
[FK_Customer_Person_PersonID]

However,both of them gives a very different result set.But my question is why do we need to use the Customer.PersonID instead of Customer.CustomerID. Is it really important to use one primary key and one foreign,is there any specific reason why the book showed c.personId=p.BusinessEntityId.??

View 3 Replies View Related

List Primary And Foreign Keys

Jan 10, 2006

I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.

Your help would make my life a lot easier

thanks

View 1 Replies View Related

Create A Relational Diagram From Non-relational Database

Aug 4, 2005

Hi all,
I am trying to create a diagram for our database, during the creating, I create some of the relationships which were not there(basically our original database is not relational database, that's why I am doing it)
So sometimes I have to chage data type in order to create a relationship for the coloumns in different tables. i.e. change char(16) to varchar(7) (I checked the field that make sure all the data in this field is <= 7 characters)

But when I saved the diagram, there is an error message that state:
Errors were encountered during the save process. Some of your database objects are not saved on your diagram.

'agent' table saved successfully
'VisitUSA' table
- Unable to create relationship 'FK_VisitUSA_agent'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VisitUSA_agent'. The conflict occurred in database 'CMC', table 'agent', column 'AgentCode'.

What does that mean? is it caused by some of the agentcode data in VisitUSA table which is not in agent table?
Thanks!
Betty

View 3 Replies View Related

SQL 2012 :: Simple Way To Remember Primary And Foreign Key?

Dec 15, 2014

What is the simple way to remember Primary and foreign key?

Why is the ID column in a table declared as a integer datatype?

View 2 Replies View Related

Script To Build Back Primary & Foreign Key.

Jul 3, 2007

Hi there,

I'm facing a problem that errors occurred when i scheduled the job running a DTS, so i removed the primary & foreign key and the DTS is running successfully. Now, the problem is, i need to build back the primary and foreign key, can anyone assists me how to write a script to build back these two keys?

Please help!!

Thanks in advance!!

View 2 Replies View Related

Primary/Foreign/Identity Keys && Encryption

Nov 2, 2006

Hi all!

I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:

== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)

== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)

== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)

The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?

Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?

Any ideas are appreciated.

Thank you,
Ben

View 1 Replies View Related

Foreign Keys Between Databases

Jul 20, 2005

I realize that FK cannot be created across databases. The question Ihave is what are the alternatives?In my thinking I have two choices.1) First choice (which I read in a different post) is to use a storedprocedure as a constraint to go check the table in the other database.2) Second choice would be to set up publish/subcribe and replicate thetable across the databases. THEN set up a Foreign Key.Is there a pro or con (besides having two copies of the table with thesecond choice) to either choice? Is there a better way?

View 4 Replies View Related

Updating Tables In Sequence With Primary Key And Foreign Key Relations

Feb 7, 2007

Hi all,
       In my project i will have the  data in a collection of objects, I need to update series of tables with foreign key relations
       Right now my code looks like this
       foreach(object obj in Objects){
       int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table
       int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId
       ///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table
       }
      The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.

Thanks
Regards
Prasad.

View 2 Replies View Related

Using GUID Fields As Primary Keys W/ Foreign Key Constraints

Nov 17, 2005

Ok, so I've broken down and decided to write myself an invoicing
program. I'd like to use GUID fields as the Primary Keys in my
table. Now, I basicly bill for two seperate items:

Deliverables and Services.

So, my Layout's gonna look something like

Create Table Invoice(
     ID UniqueIdentifier Primary Key,
     -- Other Data
);

Create Deliverable(
    ID uniqueidentifier Primary Key,
    ParentInvoice uniqueidentifier,
   -- Other data);
--...

Im sure there are probems with that as it's written feel free to edify me as to what they are.

my questions are such:

1) Does a uniqueidentifier field automagically get a value?
2) If not how do I generate one in TSQL?
3) If so, what do I use to store my Foreign Keys.
4) How do I declare my Foreign key constraints?

View 12 Replies View Related