Easy Problem, But Need Help With Setting Up Database Keys.
Jan 29, 2008
I need help setting up my primary keys for a database table. Suppose I have two tables, as listed below. The first one is
a table of students and the course they are enrolled in. The second table is a table of test scores. Never mind that this
could be designed better. Please just using my design as an example, I need help.
First table - Student Table
Student Id Student Course Building
1 Joe Math101 Primus Hall
2 Jake Engl103 Indigo Hall
3 Mary Art107 Money Hall
So my question is the following. On my second table below, can I just have
one Key as the Primary key? Just like in the first table. I will have a
primary key named "TestId"
If so, how do I set up my table so that the values in the "TestId"
table will be automatically generated upon inserting into the Test
score table.
Second table - Tests Score Table:
TestId StudentId TestScore Date
1 1 99 9/1/07
2 1 100 9/15/07
3 1 88 9/16/07
4 2 99 9/1/07
5 2 100 9/15/07
6 2 88 9/16/07
7 3 79 9/1/07
8 3 100 9/15/07
9 3 100 9/16/07
View 1 Replies
ADVERTISEMENT
Dec 19, 2001
Hi,
is there a way to set permissions on a database so that a certain user can view the design of all the tables, sps and triggers etc but not be able to view the data. If so, how do you do that?
thanks
Zoey
View 1 Replies
View Related
Jul 6, 2007
Hi,
I received the error below upon saving when I was trying to create a foreign key in a table using relationships in "table designer" of sql server 2005 management studio. This process has been successful for other tables. What might be the problem?
Thanks
Code Snippet
- Unable to create relationship 'FK_SAMPLE_GRID'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_GRID". The conflict occurred in database "acquire", table "dbo.GRID", column 'GRIDNAME'.
View 1 Replies
View Related
Feb 12, 2008
Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
View 2 Replies
View Related
Jul 20, 2005
Is there an easy way to monitor (audit) who logs onto a database ??Thanks for any and all help that is provided.Art
View 2 Replies
View Related
Jun 16, 2006
My MS-SQL 2000 Database have 50 more Stored Procedure .How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQLDatabase?
View 1 Replies
View Related
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
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
May 16, 2008
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?
View 2 Replies
View Related
Feb 1, 2007
Hi all,
I am using C# for ASP.NEt 2003.
I would like to know if there is any easy method to update a database with about 100 fields in it.
At present, I pass all the values of the controls on the web form to the stored procedure as parameters like :-
myCommand.Parameters.Add("@CustomerID", SqlDbType.Int).Value = txtCustomerID.text
Like this, I add all 100 parameters.
Is there any easy method to do it using a class or any other methods?
Thanking you in advance,
Tomy
View 2 Replies
View Related
Jul 15, 2002
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
View 1 Replies
View Related
Nov 22, 2007
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
View 6 Replies
View Related
Apr 23, 2002
Hello!
I am Paolo, a 3rd year college student taking up computer science. I am currently doing a project as part of my on the job training but am stumped! Maybe you can help! ;)
software used:
Sql Server 2000
The facts:
1. there is a CARS database on server 1 (carId, carDescription)
2. there is an issues and problems tracking database on server 2 (problemId, problemDescription)
3. I am supposed to extend the functionality of the issues and problems tracking database to include the cars. i.e. the database on server 2 should look like: (problemID, problemDescription, carId)
The question:
Is there a way i can put a foreign key constraint on server2's carID so that i can only input carIDs which are in the CARS table on server1?
The only way i can think of is to make a new table in server1 and just drag a froeign key relationship in enterprise manager's designer, but my boss doesnt want to keep both information on the same db!
Any ideas?
Many thanks!
View 2 Replies
View Related
Mar 9, 2007
Is there max number of Database Master Keys that can be used in a single instance of SQL Server 2005?
If I have 12 unique client databases that all need a unique Database Master Key, can this be accomplished?
Thanks ALL!
View 6 Replies
View Related
Apr 9, 2007
I've played with various configurations of the MS SQL Server encryption functionality, and come across an embarrassingly easy question that I cannot seem to resolve. How do I retrieve the actual symmetric and asymmetric keys out of the database?
I'd like to explore the possibility of off-loading the encryption/decryption work from the database server to a load-balanced pool of servers. For this model to work the pool would need access to the keys. The symmetric keys currently are generated with the command...
create symmetric key EncryptionKey211
with algorithm = AES_256
ENCRYPTION BY certificate CreditCardCert
Am I missing something obvious here?
View 4 Replies
View Related
Sep 26, 2007
Is there a limit on number of symmetric keys/asymmetric keys/certificates that can be stored in a database?
Is there any effect on performance of the SQL Server if I have too many (few hundreds) symmetric keys in the database?
thanks
View 1 Replies
View Related
Sep 21, 2006
Hi,
I've been trying to export a whole database from one server to another (connected through a local network) but the keys always get lost: when I open the diagrame I can actually see them just before the tables names appear. I've tried inserting the primary keys for each table manually with the export wizard, which took me some time, but no relations were exported. Any way of doing this automatically?
View 5 Replies
View Related
Dec 22, 2006
In trying to upgrade my sql express install from express to developer edition, I ended up having to delete all things express from my computer and do a clean install of developer edition (as i installed developer edition initially over sql express but this didnt seem to work; express edition still appeared in add remove programs etc). Anyway so i blew away the install and have a clean install of developer edition. Now when I create a new website in Visual Studio 2005 and try to add a database to the project, it gives me an error stating that i need to have sql server express 2005 installed to function properly... Do i need to have express AND developer edition of sql installed to do this? I would think developer edition of sql would enable me this functionality...http://img277.imageshack.us/img277/4228/sqlerrorie2.jpgThanks!It is also worth noting that I get the dreaded remote connection error when trying to connect to a db that I used to be able to connect to when I was using sql express (the aspnetdb database that is created when you user their membership stuff)"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) "However, I did the following successfully:1. create a database using the sql management studio2. added a table and some data to it3. threw a dataset into the website4. added a gridview to a page in the website, pulling data from the dataset5. and the website runs successfully populating the page with data...fyi: My sql services: http://img158.imageshack.us/img158/2020/sqlserviceshj6.jpg
View 15 Replies
View Related
Apr 5, 2005
Hi!
I have seen two approaches to primary keys. First one - and it is like
default - is to use surrogate key as primary key. For each table I will
create some autonumeric field hat cannot be changed once it has value.
Some materials refer to this key also as technical primary key. I
design my databases this way usually.
The other approach is to create primary key of fields that make primary
key on database logical model. This approach is not so popular and has
some side effects like a little bit clumpsy looking joins and
unconvenient use in applications.
Question: What is the main idea behind second approach? Or how explain their preference database designers who are using second approach?
View 6 Replies
View Related
Apr 22, 2015
suppose if we do not have FK then what kind of advantage we could not avail. we can fetch data from two table by creating a relation in sql.....then why FK is required.
View 10 Replies
View Related
Apr 17, 2001
whenenver i run cold fusion, it automatically gets my tables out of the master database instead of the new one i have created. i've added a user that has a default to the new database instead of the master, but i need to know how to use that new user correctly, and what i will need to do once i start adding more databases so that cold fusion will go to the appropriate database and not to the master.
View 3 Replies
View Related
Apr 17, 2007
I have a database column that stores a comma delimited list of foreignkeys. Would someone show me how to do a join using the values from alist stored within a record?For example, a record in tbl_cds.genre_id might have a value of "2,5, 6" corresponding to genre_ids 2 , 5 and 6. I want to jointbl_cds.genre_id to tbl_genre.genre_id using the values in that datafield.It seems I need a loop like this:SELECT * FROM tbl_cdsWHEREBegin Looptbl_cds.genre_id[i] = tbl_genre.genre_idEnd Loop.Would someone give me the correct syntax?Is there an alternative method that would create less overhead?Sorry for such a novice post.
View 2 Replies
View Related
Oct 16, 2006
I set up a basic Transfer Database task (online, copy) to copy a DB. It works great except for the fact that it isn't transferring the PK's and FK's. It also looks like it did not transfer the views. Any idea why? Anything else the Transfer Database task doesn't actually transfer?
View 2 Replies
View Related
Oct 9, 2013
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.
View 3 Replies
View Related
Apr 7, 2007
I have been using MySQL as the back end of my .Net 2.0 applications for a while using a custom membership provider without any problems.Having now switched to a new webhost which supports both MySQL and MS SQL Server 2005 and also creating a new site thought I should set up the new site to use MS SQL..... I am having trouble however setting up the SQL server to act as the membership provider. Following the advice on here I tried using aspnet_regsql.exe... Firstly, when trying to get the list of databasses on the remote server I get the following error: Failed to query a list of database names from the SQL Server. Invalid object name 'Sysdatabases' If I then just type in the name of my database and go through to the end I get the following error:Exception:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated. I can connect to my MS SQL server remotely without any problem using the likes of MS SQL Server Management Studio Express. Is there any other way I can setup the databases needed for user and roles access control or is the usage of MS SQL much more complicated than what MySQL was?
View 4 Replies
View Related
Dec 9, 2004
. The BPA recommend that the model database setting for the items below be set to on. I can accomplish this task through the query analyzer and run the set command. (Set ANSI_NULLS on). The response is positive but when I re-run the report the setting are back off.
Why???
•QUOTED_IDENTIFIER
•ANSI_NULLS
•ANSI_WARNINGS
•ANSI_PADDING
•ANSI_NULL_DFLT_ON
•CONCAT_NULL_YIELDS_NULL
View 5 Replies
View Related
Feb 20, 2006
Hi people,
I'm in the process of trying to set up my first SQL Server database and things aren't going as per the book! (surprise, surprise).
The 'Identity Specification' option during Table creation, is 'greyed out' and won't let me set (Is Identity) to 'Yes' or anything else for that matter.
I guess that there's a simple answer to this one, but as a newbie to SQL Server ( and VB 2005) I'm curently at a loss.
Can anyone point me in the right direction?
Niknek
View 1 Replies
View Related
Mar 25, 2008
Hi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
View 4 Replies
View Related
Aug 13, 2007
Hi,
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.
Regards
Mike
View 7 Replies
View Related
Apr 4, 2008
I'm having trouble changing data in my database. It is actually a VB cuestion, but you see, i just finished lesson 9 of the begginers Visual Basic 2005 video series and in my last run i can't save the data i've changed into the database. no errors are shown, it just does't change the values.
i know for a fact that the code is well written and there is no problem with the configuration of buttons and such, because i folowed the steps 50 times so I believe it must be a SQL configuration problem.
does anybony have any insight on what the problem might be.
THX malcolm
View 2 Replies
View Related
Jan 29, 2004
we have a database that only serves Select requests (no OLTP) for our very high traffic websites.
what are the benefits of turning on read-only setting for the database if there are no transactions occuring in the database?
does sql server put locks on tables for SELECT statements?
is it the same as using HINT (NOLOCK) ?
thanks
stephane
View 1 Replies
View Related
Feb 23, 1999
Hi,
There has been a lot of discussion about moving single databases or devices in same server. Can someone suggest the most simplest method to set up a identical server with all the logins, permissions and password etc?
Building a new server with sp_help_revdatabase and dumps/loads is relatively easy but moving the master is confusing to me. I should run the new server and databases for a little testing period (with diffrent server name, of course) and then go live by dropping the old and by adding the new. I remember someone said that this would be as easy as copying master.dat to new machine (when servers are identical), any experience or other suggestions?
TIA,
Al
View 2 Replies
View Related
May 9, 2006
Hi all.
Does anyone know how to set a database priority? I have a SQL Server that contains multipule databases. One of the databases is very high priority. I need to make sure that no matter what is running on the server that this database's processes take priority and nothing slows it down.
Thanks.
View 2 Replies
View Related