Keys
May 24, 2004
hi
lets say i have table student(id,name) id =pk
table course(cno,cname)cn=pk
now iam a fresh graduate as i learned from uni if i want to get the couses that each student took i would make a table called studentcouse(id,cno) and put the two of them pk
now iam working and at work they told me to do so:studentcouse(studentcouse_serial,id,cno) studentcouse_serial=pk .but i told them that dublicate filed may occur amd they told me that we have a function that will remove dublicate.
so iam asking u if who is right me or them and if u can tell yr comments
thanx a lot
View 5 Replies
ADVERTISEMENT
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
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
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 24, 2000
Hi,
Can anybody of u guyz help me out of this. I transferred my tables(about 250) to another server using DTS. But all my keys got dropped. I tried using Replication and still the destination is without my keys. What i need to do get my keys? Do i need to take a script of my keys and execute it there in destination server? If so, everytime i need to do that? Please help me out in these problem..
I really appreciate any help amidst your precious schedule..
Thank you very much
Rose, Shania
View 3 Replies
View Related
Nov 15, 2006
"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."
can soemone explain to me why i have this kind of error?
i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!
all the values from of application_input will be stored also in approve_overtime.
sometimes the datas can be stored.sometimes it cannot and produces an error!
what do u think?
hmmm pls help!
View 1 Replies
View Related
Apr 17, 2007
Can any body tell me how to know to what columns of other table it refers to.
shiva kumar
View 2 Replies
View Related
Apr 3, 2008
Hi,
I am currently playing with Sql Express 2005, and I am wondering if there is any way to create secondary keys on a table?
I know you can create compound Primary Keys (not sure that is the correct terminology), and Foregin Keys. However, I am unsure about secondary keys ( compound or otherwise).
Could someone tell me if this is possible. I am looking at migrating from an Acucobol Vision file system, which makes heavy use of secondary keys.
Cheers
View 5 Replies
View Related
May 26, 2008
i have some confusions with keys and indexes.. plz let me know whether the following are correct..- Every Primary Key is a Clustered Index- A Primary Key cannot exist without a Clustered Index- Every Unique Key is a Non-Clustered Index - Non-Clustered Index is the DEFAULT Index- A table can have only 1 Primary key- A table can have only 1 Clustered Index- A table can have any number of Unique Keys- A table can have any number of Non-Clustered Indexes
View 5 Replies
View Related
May 16, 2002
Using SQL Svr 7.0. It appears that primary keys are created as nonclustered
unique indexes. Is there a configuration setting I can use to make them be
created as clustered unique indexes?
View 1 Replies
View Related
Apr 4, 2001
If a table has a column defined as Int, Identity(1,1) which is to be used as the primary key, should that index be defined as clustered or non-clustered? In Enterprise manager when you create a PK on a table it defaults to being a clustered index. I am sure the answer depends on the other index requirements and columns in the table but I'd like to see what other ppl think about this.
Thanks!
View 4 Replies
View Related
Mar 14, 2000
I have read that SQL Server tables can't have more than one primary key. I know in Access two keys are allowed. Why can't there be two primary keys in a single table in SQL Server 7.
Thanks
View 1 Replies
View Related
Aug 24, 1999
Could someone enlighten me as to the advantage of using the foreign key tab when in table design mode in the Enterprise Manager. Does it have any advantages ?? Is it necessary ??
thanks in advance
Paul
View 1 Replies
View Related
Mar 29, 2001
I haven't tried, but does anyone know if its possible to a have a foreign key for two tables when the tables reside in different databases (on the same server)?
Thanks,
Doug Smith
View 1 Replies
View Related
Oct 25, 2001
l'm trying to do inserts on tables with foreign keys and they keep crashing. Can somebody please help.Whats the best way of populating data that has foreign keys?
View 1 Replies
View Related
Jun 15, 2004
Hi everyone,
Does someone knows how can I drop a primary key (that I don't know the name) from a table in one sql statement.
Thanks,
Fady
View 3 Replies
View Related
Oct 4, 2004
How would I drop a foreign Key?
Thanks
Lystra
View 1 Replies
View Related
Nov 10, 2004
Hi all-
I have recently come across a DB Schema without about 300 tables, none of which have any keys associated with them. What I did notice however is the schema has 3000 or so SProcs which is handling most of the validations.
Does anyone know of any advantages/disadvantages to this approach? I have always been around DB Engineers who have stressed upon having keys for Table level integrity.
Thanks for your time.
View 5 Replies
View Related
Feb 19, 2004
HI ,
I accidently removed the primary keys from my table by mistake. Is there anyway ,That i can get the PK's back to what is used to be. Need Help pls...... When I try "resetting" the PK I kep getting this error:
'table_name' table
- Unable to create index 'PK_tablename'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'tDetail' has been created but its maximum row size (12521) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
View 4 Replies
View Related
Mar 10, 2004
I need to know if this is required? I have products, with the help of their business account numbers, are naturally categorized numerically. I want to create a product category table and a product account table.
Example :
tbProductCategories
TypeCode | Description
1000 | Cups
2000 | Plates
tbProductAccounts
Account | Description
1001 | Mug
1002 | Glass
2001 | Plate
2002 | Saucer
With the above tables (which are made up :) ), would you include
a foreign key in tbProductAccounts indicating the type code?
What would the stored proc look like without it?
Create Procedure usp_GetProductAccounts
@iTypeCode int
AS
SELECT tbProductAccounts.Account,
tbProductAccounts.Description
FROM tbProductAccounts
WHERE tbProductAccounts.Account - iTypeCode > 0 AND < 999
Would this work? Or should the foriegn key always be included?
Mike B
View 4 Replies
View Related
May 17, 2004
In access you can have two fields that are primary keys with one or the other repeating as long as the combination is not repeated.
i.e.
key1 key2
200410 12345
200410 12346
200410 12588
etc for all 200410 there can not be a repeat of any value of key 2
is there a way to have this dual key in MSSQL :confused:
View 1 Replies
View Related
Jun 8, 2008
I am creating a series of foreign keys in my new database, and so far everything is going fine. The company I work for never uses them, and I am working on my own stuff on my own time now.
I come across one table that I do want one field to allow zeroes, but when its set to a value, to exist in the other table.
To be more specific. I have a PO type table, which has an EmployeeID of the person who made the PO, and another field to store who received the PO when the order arrives. I want that 2nd Employee field to be zero until it's received.
I created an employee of zero, to allow the foreign key to be created. But all the other tables that have foreign keys to my Employee table I would prefer to not allow zeroes. So I changed them to use a check of (employeeid > 0).
Is it possible to have a foreign key say that I want the value from the Field in Table A to exist in table B, or to be zero? Or would it just be easier to leave off the foreign key in this one case?
Tks
View 4 Replies
View Related
Feb 13, 2006
Hi
How can 2 columns be clubbed together to form a primary key , i mean I have 2 columns job & Batch , need to club them together to form the primary key
How is it done I mean in the design , how to define that both the columns togethter form a primary key ,
Cause when I go to the table , it allows me to create a PK through the EM only for one column
Please help
View 3 Replies
View Related
Dec 15, 2006
Shiry writes "Hi, I'm a beginner and I'm a bit stuck here..
I'm creating this database for my homework, I'm using Marks & Spencer as an
example. It has a table, products, for the clothes
id
name
cat_id ....... connected to typ_cat
colour_id ...... connected to typ_colour
size_id ....... connected to typ_size
price
sup_id ....... connected to typ_sup ...... but here i'm stuck a bit, are you
allowed to connect a typ_table to another typ_table?
this is the typ_sup:
id
name
address
city_id ......... connected to typ_city
or rather have a separate sup table with the same id, name, address, city_id
id will be connected to typ_sup
and the products table.. sup_id will then be connected to typ_sup..?
What way is better? and is it allowed to connect a typ_table to another
typ_table?
Thanks in advance!!!!
Shiry"
View 1 Replies
View Related
Jul 23, 2005
I am using Access as a front end to SQL and have a combination of twofields set as primary keys.I have two tables, and am trying to delete all occurences of thecompound primary keys in one table, that do not occur in the secondtable.We can call the columns for the compound primary key Field3 and Field10and the tables TableDel and TableRef. TableDel is the one to deleteitems from, and TableRef is the table we reference for identifyingwhich items should be deleted.This query produces the rows that I want to delete, but I can't figureout how to make this a delete query.SELECT TableDel.*FROM TableDel LEFT OUTER JOINTableRef ON TableDel.Field3 = TableRef.Field3 ANDTableDel.Field10 = TableRef.Field10WHERE (TableRef.Field10 IS NULL)Any help would be greatly appreciated.
View 2 Replies
View Related
Jul 20, 2005
Hello,I plan to create a table with 3 unique keys.Combination of three fields has to be unique for each row in a table thatare vendor ID (char 8), vendor name (char 40), and vendor office (5).Will it be okay to have a unique key which has a long character such asvendor name?How should I index those three fields? Those fields will be searched manytimes.RCW
View 2 Replies
View Related
Jul 20, 2005
Hi,My application needs to calculate the sort order of an index key(whether the index key is descending or ascending). The user mayconnect to MS7 or MS2K servers.As far as I know, the descending indices are supported in version 8i.e SQL 2000. In MS2K, I can get the index key information by SELECTINDEXKEY_PROPERTY(table_ID , index_ID , key_ID , IsDescending) whichreturns 1 if key is descending. But as this is only for MS2K, it willfail and report error for above query in case connected to SQL server7.Now, in the application we avoided using server version dependentcode. So, can you please suggest me other way to do the same so thatthe query should not produce an error if connected to MS7 server butshould work for MS-2000 server.Regards,Uday
View 1 Replies
View Related
Oct 11, 2006
I have created two tables:
UserInfo:
UserID (PK)
Hairid
eyeColorid
faceShapeid
and
HairInfo:
HairInfoID (PK)
Hairid
HairDesc
Now I want to have a one to many relationship between UserInfo and HairInfo. I want to specify Hairid as the foreign key in the HairInfo table. Here UserInfo is the parent and HairInfo is the child. I am using SQL server 2000. Is there a way to do it using the Enterprise manager interface. Can someone run me through the steps to do it.
Thanks...
View 1 Replies
View Related
Feb 5, 2008
Hello:
I have the followig table that has constraints:
CREATE TABLE [dbo].[MD_HIERARCHY](
[MedDRA_Version_No_C] [varchar](10) NULL,
[PT_ID_N] [bigint] NULL,
[HLT_ID_N] [bigint] NULL,
[HLGT_ID_N] [char](18) NULL,
[SOC_ID_N] [bigint] NULL,
[PT_C] [varchar](100) NOT NULL,
[HLT_C] [varchar](100) NOT NULL,
[HLGT_C] [varchar](10) NOT NULL,
[SOC_C] [varchar](100) NOT NULL,
[SOC_Abbr_C] [varchar](5) NOT NULL,
[Null_Field_C] [char](1) NULL,
[PT_SOC_ID_N] [bigint] NULL,
[Primary_SOC_Flag_C] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_14] FOREIGN KEY([MedDRA_Version_No_C], [PT_ID_N])
REFERENCES [dbo].[PT] ([MedDRA_Version_No_C], [PT_ID_N])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_14]
GO
ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_15] FOREIGN KEY([MedDRA_Version_No_C], [HLT_ID_N])
REFERENCES [dbo].[HLT] ([MedDRA_Version_No_C], [HLT_ID_N])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_15]
GO
ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_16] FOREIGN KEY([MedDRA_Version_No_C], [HLGT_ID_N])
REFERENCES [dbo].[HLGT] ([MedDRA_Version_No_C], [HLGT_ID_N])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_16]
GO
ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_17] FOREIGN KEY([MedDRA_Version_No_C], [SOC_ID_N])
REFERENCES [dbo].[SOC] ([MedDRA_Version_No_C], [SOC_ID_N])
GO
ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_17]
GO
ALTER TABLE [dbo].[MD_HIERARCHY] WITH CHECK ADD CONSTRAINT [R_18] FOREIGN KEY([MedDRA_Version_No_C], [PT_SOC_ID_N])
REFERENCES [dbo].[SOC] ([MedDRA_Version_No_C], [SOC_ID_N])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MD_HIERARCHY] CHECK CONSTRAINT [R_18]
But when I click on the constarinst folder, it's empty. However, these constrainst show up under Keys.
Can somebody tell me why? Does this tell me that keys and constraints are one and the same?
venki
View 3 Replies
View Related
Oct 17, 2007
Hi there,
is there any way I can use INSERT so if I try to copy duplicate primary key data, it automatically skips the task and moves on to the next data?
View 11 Replies
View Related
