(Simple) SQL Question Regarding P/F Keys And Inserting Data...
Jul 20, 2005
Hi, all:
I'm a newbie trying to understand the concept of referential integrity and
dealing with Primary and Foreign Keys. I'm sure mine is a simple problem...
I've created 3 tables as follows:
MemberTable
-----
1 Member ID (Primary key)
2 Member Name
3 etc...
FoodsTable
-----
1 Food ID (Primary key)
2 Food Name
MemberFoods
-----
1 Member ID (Foreign key)
2 Food ID (Foreign key)
Now, I've just learned about referential integrity and all that, and this is
my first foray into creating primary/foreign keys, linking tables, etc. (so
I'm assuming the above tables are fine). What I don't understand is this:
if I have a (checkbox) form from which members can choose their favorite
foods, how do I insert that data? For example:
Food Form
-----
Which of these are your favorite foods:
1) Steak
2) Chicken
3) Potatoes
4) etc...
If I want to insert, let's say, 'Steak' and 'Chicken', do I have to first do
a query on my Foods table to retrieve the Food ID's for Steak and Chicken,
and then do an "insert" using those ID's into my MemberFoods table? (I am
using the actual Food Names (not ID's) as values on my form.) If so, can
someone please share the SQL for retrieving and then inserting?
I don' t know...maybe I'm overcomplicating the issue, but it just seems like
a lot of extra work to maintain a "linking table" of foreign keys.
I have set a common lookup + insert dataflow. but I'm having a problem when the business key comes duplicated in the source file. This file has transactions and each transaction has a transaction code which represents what kind of transaction it is. So in the same file I can have 2 or more transactions with the same transaction code.
I have a dimension table wich stores kinds of transactions with a business and surrogate(identity) key.
The lookup sends through the error path every record it doesn't find its business key. but since I may have 2 or more transactions with the same business key in the same source the lookup sends them every time so I end up with duplicated business keys.
I have tried setting the max insertion commit size to 1 but it doesn't work. The look up won't find the new business keys.
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,
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,
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?
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?
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
Hi, I would like to encrypt data in my database. I want encrypted column value to be viewable only for certain group of users. Users that has access to my database doesn't meant they can access to my encrypted data.
Currently, I am using the following "approach" as my key management.
create master key encryption by password= 'MasterKeyPass'
CREATE ASYMMETRIC KEY MyAsymmKey AUTHORIZATION MyUser WITH ALGORITHM = RSA_1024 ENCRYPTION BY PASSWORD ='MyAsymmPass'
CREATE SYMMETRIC KEY MySymmKey WITH ALGORITHM = DES ENCRYPTION BY ASYMMETRIC KEY MyAsymmKey
My data will be encrypted using Symmetric key MySymmKey.
User who want to access my data must have MasterKey and MyAsymmKey password. Is it OK? Any better way?
Hello!I have a table A with fields id,startdate and other fields. id and startdateare in the primary key.In the table B I want to introduce a Foreign key to field id of table A.Is this possible? If yes, which kind of key I have to build in table A?Thx in advance,Fritz
I'm trying to merge two Access databases into one SQL server database. I have 3 tables that are all related with primary and foreign keys.
When I try to import my second set of 3 tables I get errors about the keys already existing in the database. Is there any way to force SQL server to assign new keys while preserving my existing relationships? Thanks!
Hello. I have a problem that spans VB.net, SQL Server and SSIS but is rooted in the need to encrypt column data in SQL Server.
I would like to encrypt data that I am bringing into SQL Server in the Data transformation script component of an SSIS package. I have achieved this but I can't decrypt the data because the keys don't match. I would like to use symmetric key encryption but I don't see how to get the symmetric key that I created in SQL Server available to the VB.net script component in SSIS.
Please advise me if my approach is correct and what steps I need to take.
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1. Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID) This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
I am having data where there are empty string in the business keys which should be used for Slowly changing dimesnion type 2, how do i over come this as due to empty strings i am getting new rows even though the rows havent really changed.
example of data is name and salary are business keys
name salary age address dev 23 klddldldlk sdfg 24 34 kdlddlkd
when the same is given as input the row dev 23 klddldldlk is coming as anew row where it already exists how do i over come this
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
Hi GuysOff late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?Thanks
I'm trying to move specific data from three linked tables on a source database to three tables on a destination database by generating dynamic SQL INSERT scripts but am getting stuck on the last set of INSERT statements. I don't think I can use SSIS because the source autonumber fields may already exist on the destination side but I could be wrong.
For simplicity, Table 1 (T1) has one autonumber key of PK1 as well as other fields (A1, B1, etc.).
Table 2 (T2) has one autonumber key of PK2 and a foreign key (FK1) that links back to PK1 as well as other fields (A2, B2, etc.).
Table 3 (T3) has one autonumber key of PK3 and a foreign key (FK2) that links back to PK2 as well as other fields (A3, B3, etc.).
Like this: T1: PK1, A1, B1, etc. T2: PK2, FK1, A2, B2, etc. T3: PK3, FK2, A3, B3, etc.
So, I'm able to query the source database T1 to generate my dynamic SQL INSERT statements that will be run on the destination side. I'm also able to generate my dynamic SQL statements to insert into T2 but when I get to T3 I currently am stuck figuring out how to insert because the destination side is unable to match it's FK2 to the just inserted PK2. It throws the below error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have some website work lined up and it involves some simple modifications to a MS SQL 2000 server. What I'll need to do is add some new data fields and insert some data.
I have some experience with databases - MS Access and MySQL, but I have never used or seen MS SQL 2000. My question is, is this a relatively simple thing to do for someone who hasn't used it before? I can do these things quite simply in Access or MySQL, so is MS SQL 2000 going to be any different?
Also, does anyone know of any free tutorials online that would help me out?
Hello friends.... I am looking for 2 things(using c#.net or vb.net and sql svr 2000) 1.convert data from sql server 2000 database (say customers table from northwinds database) to a text file(separated by commas or just plain space) 2.Insert the data from text file back to database. Can someone pls give me the detailed code to achieve this....really need this on urgent basis.......Thank You.
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Hi, I use lookups to map surrogate of level 1 dimensions to my fact tables in SSIS. But how to handle a level 2 dimension with a ValidFrom and a ValidUntil date field? I do not use an IsCurrent column, because this could problem with late arriving facts.
- In dts I used an SQL statement like this:
update SA SET SA.DimProdRef = Dim.RecordID FROM SAWarenEingang SA, DimProd Dim where SA.ProduktNumber = Dim.ProduktNumber and SA.ArtikelkontoBewegungsdatum between Dim.ValidFrom and Dim.ValidUntil
Now in SSIS I want to handle the whole thing in the data flow without using a staging table: - Using Lookups: I would have to pass the date column for each inside the fact table into the lookup. That does not work. - Using Execute SQL in the data flow: would be very slow, because the statement will be executed for any line in the dataflow
This problem is a bit weird but I'm just wondering if anybody else experienced this.
I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.
Just wondering if anybody else experienced this problem and of course if there's a way to solve it.
Hi,I m using Microsoft Visual Studio 2005 and SQL server 2000. I have 2 textboxes and a button, what i wanna do is, when i hit the button, the values in textboxes should be inserted into DB. Would you please help me? Thanks in advance.
Hi friends,I have one text box on my form.i need to insert the data from tat text box to DB without clicking on any button.Now i have written the code under text changed event of that text box.So it is inserting whenever i click on that form.Besides this i need to insert data when i close tat window.But it is not inserting when i close tat window.Plse help me.Thanks in advance With RegardsLijo Rajan
I have created a form with several fields etc and validation.
After pressing the submit button i have a
if Page.IsValid then .....etc But in this then bit I want to do a
INSERT the form details to the db.
I have done inserts etc via gridView etc but I just need a form that lets someone enter info and submit etc, so do not now where to or how to place this code to connect then insert etc
I'm having a problem saving the information from my web form into my sql database when I clicked on the 'Submit' button. This is the error message Server Error in '/Helpdesk' Application.
ExecuteNonQuery: Connection property has not been initialized. I've attached my code below. Please advise me on what is wrong... How should I initialise the ExecuteNonQuery. ========= start code ==================== Dim MySQL As String MySQL = "" Dim MyConn As SqlConnection = New SqlConnection() Dim MyCmd As SqlCommand = New SqlCommand() MyConn.ConnectionString = "Server=ESAWEB2;Database=Helpdesk;Trusted_Connection=True;" MySQL = "INSERT INTO TBL_TROUBLE_TICKET (Priority) values (' ddlpriority ')"
I am not looking for free code but this is driving me out of my mind. I'm a pretty proficient PHP programmer and have been dealing with a form that I was made to program in ASP.Net due to my employer's preferences. I can't attach the code for the form so I have cut and pasted it below. I am needing to know whow do I code this so that the data will go into a MS SQL 2005 database? I already have some coding in it but I don't know if it's correct and any help in getting this fixed would be great as it is slowly driving me up the wall. Thanks!
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim conebackups As SqlConnection Dim strInsert As String Dim cmdInsert As SqlCommand
I'm trying to insert a record into a master table, but b/c of the foreign key relationship, I know that I need to first insert a record into the child table so I don't get a foreign key error. The problem is that the record that I'm inserting doesn't have any columns that match up (similar)to the child table, and only a few that match up to the master table.
Hi,Is there any way to insert the output of xml_auto into a tablefor eg:select * from categories for xml autoi need the output of the abouve query to be inserted into another tablethe destination table has one column,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO ======================================= -- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO ========================================= -- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables?
I need to create a stored procedure that will insert data with some already exsisting data in a table. The data is in a spreadsheet, my issue is that I dont want to violate the primary key rules.
can anyone help please
CREATE PROCEDURE InsertTerms AS INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #], [FirstName], [LastName], [SocialSecurityNumber], [DateHired], [Status], [Title], [DepartmentName], [Pictures])
SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS FROM EmployeeGamingLicense AS a WHERE a.STATUS = 'TERMINATED' IF @@Error <> '0' RETURN
Hi. We have a sql server db that we need to store Unicode text in. The fields are of the type nvarchar, ntext and nchar. Our solution uses both Oracle and SqlServer as a backing database. In Oracle there is a connection string switch "Unicode=True" that fixes the problem. Is there something similar in SqlServer? Since the db layer is generic we'ed like to avoid using a N' prefix on text strings in query statements.
I am trying to transition from Access to MS SQL. I downloaded and installed MS SQL 2005 Express and the Server Management Studio Express. Keep in mind that I am a total newbie to this database since I've always used Access. My question is, how do I insert data manually into my tables? I figured out how to set up a table and query a table, but is there somewhere I can just plug in data? In Access, I just open the table and type it in. Also, how do you set an auto increment numeric field in a table?- Jason
