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,
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?
I've attempted to identify a primary and foreign key in these two tables, but I am getting a bunch of errors re duplicate keys and column names needing to be unique.Perhaps the primary and foreign key I have identified don't meet the criteria?
CREATE TABLE StockNames ( -- Added Primary key to [stock_symbol] [stock_symbol] VARCHAR(5) NOT NULL CONSTRAINT PK_stock_symbol PRIMARY KEY, [stock_name] VARCHAR(150) NOT NULL, [stock_exchange] VARCHAR(50) NOT NULL,
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'.
I am trying to execute following sql script in sql-server 2000 query analyzer
CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY, course_id varchar(10) NOT NULL PRIMARY KEY)
Its give's me following error :- Cannot specify multiple primary key constraint
Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.
Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys. Will it allow me to have following records in the user_courses table? user_id(P.K) course_id(P.K) bob CRS235 alice CRS235 Tim CRS235 tom CRS635
So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?
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 have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?
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.
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.
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.
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
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<', '')
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:
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?
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?
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
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?
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.
There is itemlookup table, which stores item number and itemdescription. Also there is a child table, pricehistory, of theitemlookup table, which stores different prices and different dateranges. So it is one-to-many relationship. (Price can be stored morethan one with a different date range)And there is another table RequestItem that stores the foreign key ofthe itemlookup table to show the information of the itemlookup table.Then how do I know later which date range of the price was stored inthe RequestItem table? Since I only keep the foreign key of theitemlookup table, it will be impossible to keep track of the row of thepricehistory table if there are more than one data existed in thepricehistory table.Will it be a valid table structure to create a column for the foreignkey of the pricehistory in RequestItem table or any other ways tohandle this issue?
I have two tables as below. I want to update a quote and change the item for which it is for. So I want to do an update statement to change the cat_ref that the quote is for. However, when I do this I get a foreign key conflict with cat_ref in the item table. How do I get around this? Thanks
I have a very elementary question on table design. This screenshot (which is part of a larger diagram) sets the context of the question.
There are two distinct groups of products with different attributes, hence two separate tables: Products_A and Products_B. There's a third table that keeps track of daily production (DailyProduction), which must accept items from either product table. Now, my question is: How do I relate these two tables to "DailyProduction" ? If I place a double-constraint with two foreign keys, then I get an error whenever I attempt to insert a record in DailyProduction. For example, if the new item is of "Products_A" type, then the error message from SSMS is:
No row was updated.
The data in row 1 was not committed. Error Source: .Net SqlClient Data Provider. Error Message: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DailyProduction_Products_B". The conflict occurred in database "MyDatabase", table "dbo.Products_B", column 'ProductB_ID'. The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
It's clear that the error message makes sense as SQL is trying to apply BOTH constraints. It's one AND the other, not one OR the other. Now, how do I remedy this ? What would be the most appropriate way to relate the three tables ?
i'm using sql express, management studio express and a visual web developer starter kit. i have 4 tables: items; categories; categorization; old_items the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys. i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it. as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self. i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them. can anyone help? i've been trying all the permutations of queries i can think of and not getting it. thanks.
Kinda new to SQL, using SQL Server 2005.I have some foreign keys in a couple of tables. I need to drop thesetables, but can't since I'll get the error:Msg 3726,Level 16, State 1, Line 1Could not drop object 'Client' because it is referenced by a FOREIGNKEY constraint.I'm not sure how to disable or get rid of these foreign keys so that Ican drop my tables. I tried:ALTER TABLE Client NOCHECK CONSTRAINT ALLAlter Table Client Drop Column Foreign Key Boss_ID;I went into the Object Explorer and deleted the FK lines from eachtable, but still the same error.What am I doing wrong?Thanks for your help.
Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!
Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.
Let's say you have a Users table, which simply contains a collection of people. Each person has attributes like their password, first and last name, username, etc. It would be useful to track changes to that table over time, so that if an entry is changed, you can simply look at a history table and see which changes occured, and when.
An INSERT/UPDATE trigger works well for this sort of thing, where the trigger inserts all of the 'INSERTED' values into a history table that has basically the same table definition as the Users table. But it gets tricky when considering Deletes.
If my History table entries reference back to the User in the Users table, this means that if I ever want to delete the user, I need to delete all their History first. This means I can't keep records of user deletions, which is significant. The other approach is not to have a foreign key reference in the History table, so that if a user is deleted, I can still keep my History about that user. Including deletes.
I have been timid about doing it this way, since I felt it broke the idea of a well structured database. Is there a better way to approach this? Is there a standard way to set up triggered history to track changes, including deletions, from a table?
Hello,I have 2 tables:- Customers with columns:customerID(prim_key),customerName(with customer's names)- Deliveries with columns:deliveryID(primKey),sender(ref_key to CustomerID from Customers),receiver(also ref_key to CustomerID from Customers);I need to select all data about deliveries, but instead of havingsender's ID and receiver's ID, I need to have their Names.I tried to do:SELECTdeliveries.deliveryID,Customers.customerName AS sender,Customers.customerName AS receiverFROMcustomers, deliveriesWHERECustomers.customerID=Deliveries.sender ANDCustomers.customerID=Deliveries.receiver;But this only works if sender=receiver, which is obvious ;)I'd like to know if there is any other way for obtaining those datawithin one queryThank you very much for your helpChris
I am still new to SQL and I am having trouble obtaining the results I need from a query. I have worked on this command for some time and searched the internet but cannot seem to still get it correct.
I have a table called Patient. It's primary key is pat_id.
I have a second table called Coverage. It has no primary key. The foreign keys are pat_id, coverage_plan_id, and hosp_status.
I have a third table called Coverage_History. It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from.
I want to get the pat_id and all the coverage information that is current. The coverage table contains specific insurance policy information. The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this:
Patient (pat_id and lname) P123 Monto P124 Minto P125 Dento P126 Donto
Coverage (pat_id, coverage_plan_id, hosp_status, policy_num) P123 MED1 OP A1499 P123 ACT4 OP H39B P124 MED1 OP C90009 P124 RAC OP 99KKKK P124 RAC OP 99KKKK P124 MED1 OP C90009 P125 ARP OP G190 P126 BCB OP H88
Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to) P123 OP 1 20150102 MED1 NULL P123 OP 2 20150102 ACT4 NULL P124 OP 1 20150203 RAC 20150430 P124 OP 2 20150203 MED1 20150430 P124 OP 1 20150501 MED1 NULL P124 OP 2 20150501 RAC NULL P125 OP 1 20150801 ARP NULL P126 OP 1 20150801 BCB 20160101
select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority, from patient p left join ( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and (ch.effective_to is NULL or ch.effective_to >= getdate() ) ) on ch.patient_id = p.patient_id
where ( ch.effective_to is NULL or ch.effective_to >= getdate() )
So I want to see:
P123 Monto MED1 OP 20150102 NULL 1 P123 Monto ACT4 OP 20150102 NULL 2 P124 Minto MED1 OP 20150501 NULL 1 P124 Minto RAC OP 20150501 NULL 2 P125 Dento ARP OP 20150801 NULL 1 P126 Donto BCB OP 20150801 20160101 1
I have an issue where i am mandated to enforce RI on an applications database (a good thing). but I have several common lookup tables where many of the "codes" reside for many different code types. I also have the mandate that i cannot change the underlying DDL to make composite keys to match the codes table PK. I am currently looking at creating indexed views on top of the Codes table to seperate the logical tables it contains. This is several hundred views.
I do know that I do not want to write several hundred triggers to enforce RI. Table schema below, the CdValue column is the column that is used throughout the hundreds of tables that use this codes table, and their corresponding column is not named the same.
CREATE TABLE dbo.CodesTable ( PartyGrpId INT NOT NULL , CdTyp VARCHAR ( 8 ) NOT NULL , CompId INT NOT NULL , CdValue VARCHAR ( 8 ) NOT NULL , CdValueDesc VARCHAR ( 255 ) NULL , AltValueDesc VARCHAR ( 100 ) NULL
[Code] ....
I did though run into one forum where a person brought up a great idea. Filtered Foreign Keys, what a novel concept, if it could work it would make so much less code to fix an issue like this.
ALTER TABLE dbo.BusinessStatus WITH NOCHECK ADD CONSTRAINT FK_dbo_BusinessStatus_CodesTable FOREIGN KEY (LoanStsDtCd) REFERENCES dbo.CodesTable (CdValue) WHERE CdTyp = 'Status'
I have a table fields Register_date, Std_ID, course, version, CourseLearningDays, Batch_Time and Trainer_IDHOw to make Register_date and Batch_Time as primary key in the table.. What are the necessary datatype for these two fields.And one more do we have datatype called date and time in MS SQL Server...
I load values from a TXT file into the temporary table. From there, I figure out which records are new and need to be added to the primary table, and which records are duplicates, and need to a) update the primary table and b) be deleted from the temporary table.Basically, I need to write a SQL statement to grab the records that exist in BOTH tables based upon two primary criteria:
d_id in temporary table = d_id in primary table df_t_v_stock_number in temporary table = v_stock_number in primary table
There are several fields that must be updated if the d_id and v_stock_number match:
I've created a new table in ASPNETDB.mdf called 'customerInfo'. Most of the fields are, for the moment, data that exists in one of the existing ASPNET tables eg Membership_UserId (primary key), Membership_Email, Users_UserName etc.; the list goes on. I have put a new column 'amountspent' - just to test that I can do what I think I can do, however the table doesn't display any data. Am I OK in thinking that I can use the aspnet data in my own tables and reference through foreign keys to them. I've made sure that the procedure that I used to put the table into a gridview on the webpage is OK as I did the same thing to the membership table and that displays all of its data. Can someone either explain the steps I need to make this happen correctly or point me in the direction of a noob type walkthrough. Thanks