Can Someone Critique My Sql Relationship Structure?
Jun 6, 2008
I was building out my db and then ran into a problem updating a primary key value. I then started to doubt whether or not I even did this right. To simplify, I"m going to just create a smaller similar scenario and I just want to know if its right
Lets say I have three tables
Order Table
ordernum (PK)
orderdate
orderamt
Phone Table
Phonenumber (PK)
ordernum (FK)
Numbertype
Internet Table
CircuitID (PK)
Phonenumber(PK)
IP
SM
GW
now, Instead of creating a seperate column for just an incrementing numeric value as a PK i used the order number, phone number, and circuit id's as the Primary keys since every single value will be unique. Plus, I also did it to prevent duplicates. Now the problem I'm having is, when I do Update db set [phonenumber] = @phonenumber, ordernum = @ordernum, numbertype = @numbertype where phonenumber = @phonenumber as wrong as that looks.
It looks wrong to me.. problably why it doesn't work. How do you change the values of primary keys.. or should I have created a column called ID with incrementing numbers. How would I prevent duplicates then?
View 5 Replies
ADVERTISEMENT
May 21, 2004
Here is a trigger I wrote to check for duplicate taxid's in my producer table. Duplicate taxid's with an '' value are allowed. I'm green when it comes to trigger coding so would anyone care to review my code and reply if there's a more efficient way to implement the trigger or handle errors.
Thanks PLJ
create trigger dbo.tib_producer on dbo.producer for insert as
begin
declare
@numrows int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
/* Check for duplicate tax_id if inserted is not null */
if update(tax_id)
begin
if exists(select tax_id
from producer
where tax_id in (select tax_id
from inserted)
and tax_id <> ''
group by tax_id having count(*)>1)
begin
select @errno = 50002,
@errmsg = 'Tax ID already Exists'
goto error
end
end
return
/* Errors handling */
error:
raiserror @errno @errmsg
rollback transaction
end
View 3 Replies
View Related
Feb 23, 2007
Hi,
I would really appreciate any constructive criticism of my sql script (see end of message).
I have been working for a week to pull data from a database that has evolved over the years. It might have been designed to begin with, but it has had a lot of changes made on the fly, which makes pulling some data from it very hard.
One example is that they wanted Primary Service for a given client but this data was not stored in a base client table, but was instead on a table with multiple rows per client. To pick the row that referenced the correct Primary Service I had to:
1. Join the Client table with the view on client number and primary program number
2. From the records returned look at another column (proc_cde) in the view and pick the one that had a higher priority than the others. The precedence order of the proc_cdes was determined by a list my boss gave me. I used this forum to help me figure this part out.
Anyway … I finally got it done and it works … but just because it works doesn't mean it is well written. What I would like is if some of you could take a look at the sql and critique it for good and/or bad practices.
The design of the database is a given (and not my doing) so I am not looking for a critique of it.
An Example of what I am asking for would be:
I ended up using temp tables to collect some of the more complicated data so that I could write a very straightforward final query. Was this a good or bad thing and if bad why?
I really want to get good at sql and avoid developing any bad habits, so please critique my sql.
Also please ask me any questions that could help you evaluate the code.
Thanks,
Laurie
Note: the comments at the top were all the specs I got for creating this report (they came in an email).
/*detail- primary clincian/provider priemp_num joined to facemp Y
primary service (act,cm,opt), pripgm_num primary program,
select proc_cde from v_autsvc join svc
on proc_cde where pgm_num=pripgm_num
H0039 then T1017 t1016 then H0036
location of service, pgm_num of 90862 auth
date of auth v_autsvc.aut_dte
cons name/number: CLTCLT
auth date range v_autsvc.autbeg_dte v_autsvc.autend_dte
number of claims in the last fiscal year v_clmsvc, count(*)
primary psycchiatrist. cltdmo.pspemp_num join phy on phy_num
select clt_num, count(*) as num_claims from v_clmsvc
where beg_dte between '10/1/2005' and '9/30/2006 23:59'
group by clt_num
tables: v_autsvc, pgm, cltdmo.population='mi adult'
proc_cde in ('90862','90801')
autend_Dte>'10/1/06'
I need a report showing all Adult MI consuemrs receiving psych services by agency- like the
aggregate and detail of who gets them at where-bridgeways, sr srv... including our clinic.
i would need in the detail- primary clincian/provider, primary service (act,cm,opt), location of service,
date of auth, cons name/number, auth date range/# of units and number of claims in the last fiscal year and
primary psycchiatrist. if xxx asks for something similar, let me know as we might be duplicating.
i know that xxx is going to be asking for a report that is for just our clinic that has more to do with
insurance type. if it would be easier, you could combine the 2 reports as long as we have the capacity
to sort and print by provider. thanks*/
/**********************************************************************************
Primary Service
***********************************************************************************/
-- select primary proc_cde for primary service Candidates
drop table #ConsumerProcCodes
select distinct a.clt_num,
a.proc_cde,
a.aut_dte,
a.autbeg_dte,
a.autend_dte
into #ConsumerProcCodes
from cltdmo d join v_autsvc a on a.clt_num = d.clt_num
and d.pripgm_num = a.pgm_num
and a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte
-- create table of ProcCde Precedence
drop table #ProcCde_Precedence
create table #ProcCde_Precedence
(proc_cde varchar(10),
Precedence int)
insert #ProcCde_Precedence
(proc_cde,Precedence)
select 'H0039', 10 union all
select 'T1017', 20 union all
select 'T1016', 30 union all
select 'H0036', 40 union all
select 'T2011', 50 union all
select '90806', 60 union all
select '90862', 70 union all
select 'T1002', 80 union all
select 'H2031', 90 union all
select 'H2023', 100 union all
select 'H2015', 110 union all
select 'T1005', 120
-- assign precedence to proc_cde
drop table #ConsumerProcCodesWPrec
select distinct c.clt_num,
c.aut_dte,
c.autbeg_dte,
c.autend_dte,
case when p.Precedence is null then 1000
else p.Precedence
end as Precedence,
left(c.proc_cde,5) as proc_cde
into #ConsumerProcCodesWPrec
from #ConsumerProcCodes c left join #ProcCde_Precedence p on p.proc_cde = left(c.proc_cde,5)
-- select primary proc_cde for each consumer and get associated PrimaryService
drop table #PrimaryService
select distinct p1.clt_num,
p1.proc_cde,
p1.aut_dte,
p1.autbeg_dte,
p1.autend_dte,
s.acttyp_des as PrimaryService
into #PrimaryService
from #ConsumerProcCodesWPrec p1 join svc s on p1.proc_cde = s.proc_cde
where Precedence = (select min(Precedence)
from #ConsumerProcCodesWPrec p2
where p1.clt_num = p2.clt_num)
-- Find Consumers with more than one primary Proc_cde
/*
select clt_num, proc_cde, aut_dte, autbeg_dte, autend_dte
from #PrimaryService
where clt_num in (select clt_num
from #PrimaryService
group by clt_num
having sum(1) > 1)
*/
/**********************************************************************************
Primary Location
***********************************************************************************/
-- select pgm_num for Primary location Candidates
drop table #ConsumerLOS
select distinct a.clt_num,
a.pgm_num,
p.pgm_nme
into #ConsumerLOS
from v_autsvc a join pgm p on a.pgm_num = p.pgm_num
where a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte
and a.proc_cde = '90862'
-- create table of pgm_num for Location of Services Precedence
drop table #LOS_Precedence
create table #LOS_Precedence
(pgm_num int,
Precedence int)
insert #LOS_Precedence
(pgm_num,Precedence)
select 5600, 10 union all
select 6200, 20 union all
select 6000, 30 union all
select 1611, 40 union all
select 1612, 50 union all
select 1601, 60
-- assign precedence to pgm_num
drop table #ConsumerLOSwPrecedence
select distinct c.clt_num,
case when p.Precedence is null then 1000
else p.Precedence
end as Precedence,
c.pgm_num
into #ConsumerLOSwPrecedence
from #ConsumerLOS c left join #LOS_Precedence p on p.pgm_num = c.pgm_num
-- select pgm_num for primary location of Services for each consumer
drop table #PrimaryLOS
select distinct p1.clt_num,
case n.NumRows
when 1 then cast(p1.pgm_num as varchar(10))
else cast(p1.pgm_num as varchar(10)) + '*'
end as LocationOfService
into #PrimaryLOS
from #ConsumerLOSwPrecedence p1 join (select clt_num,count(*) as NumRows
from #ConsumerLOSwPrecedence
group by clt_num) n on p1.clt_num = n.clt_num
where Precedence = (select min(Precedence)
from #ConsumerLOSwPrecedence p2
where p1.clt_num = p2.clt_num)
-- Find Consumers with more than one primary pgm_cde for Location of Services.
/*select distinct clt_num from #PrimaryLOS
select * from #PrimaryLOS
where clt_num in (select clt_num
from #PrimaryLOS
group by clt_num
having sum(1) > 1)*/
/**********************************************************************************
Put it all together
***********************************************************************************/
drop table #FinalResults
select distinct c.clt_num,
c.fst_nme + ' ' + c.lst_nme as ConsumerName,
f.fst_nme + ' ' + f.lst_nme as PrimaryClinician,
p.fst_nme + ' ' + p.lst_nme as PrimaryPsychiatrist,
ps.PrimaryService,
COALESCE(n.num_claims,0) as num_claims,
los.LocationOfService,
ps.aut_dte,
ps.autbeg_dte,
ps.autend_dte
into #FinalResults
from cltctl c join cltdmo d on c.clt_num = d.clt_num and d.population = 'mi adult'
join v_autsvc a on c.clt_num = a.clt_num
and a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte
and a.proc_cde in ('90862','90801')
join facemp f on d.priemp_num = f.facemp_num
left join phy p on d.pspemp_num = p.phy_num
left join #PrimaryService ps on ps.clt_num = c.clt_num
left join (select clt_num, count(*) as num_claims
from v_clmsvc
where beg_dte between '10/1/2005' and '9/30/2006 23:59'
group by clt_num) n on c.clt_num = n.clt_num
left join #PrimaryLOS los on c.clt_num = los.clt_num
order by c.clt_num
select * from #FinalResults
/*
select * from #FinalResults
where PrimaryService is null
select * from #FinalResults
where LocationOfService is null
select * from #FinalResults
where PrimaryPsychiatrist is null
select * from #FinalResults
where PrimaryClinician is null
select * from #FinalResults
where num_claims = 0
*/
edited to make word wrap work
View 5 Replies
View Related
Jul 23, 2005
I just finished a new query where I summarized detail information. I'mwondering if I did this really awkwardly or is this a common way towrite SQL? I've cross referenced the end results and the data seemsconsistant, so I am happy with the results.TIASELECTSESSION_ID,CAMPUS_ID,SUM(STUDENT_COUNT) AS STUDENT_COUNT,SUM(NEW_STUDENT) AS NEW_STUDENT_COUNTFROM (SELECTSESSION_ID,STUDENT_ID,CAMPUS_ID ,STUDENT_COUNT ,STUDENT_STARTING_SESSION_ID,NEW_STUDENT = CASE WHEN SESSION_ID=STUDENT_STARTING_SESSION_IDTHEN (1) ELSE (0) ENDFROM (selectSESSION_ID,STUDENT_ID,CAMPUS_ID ,STUDENT_COUNT ,STUDENT_STARTING_SESSION_IDFROM(selectSESSION_ID,STUDENT_ID,CAMPUS_ID = (SELECT STUDENT_CAMPUS_ID FROMD_BI_STUDENT WHERE A.STUDENT_SKEY=D_BI_STUDENT.STUDENT_SKEY) ,STUDENT_COUNT = DAY0_CLASS_COUNT,(select student_starting_session_id fromf_bi_student_statistics where A.student_id =f_bi_student_statistics.student_id) as 'STUDENT_STARTING_SESSION_ID'from f_bi_registration_tracking_summary A) AS XWHERE STUDENT_COUNT > 0GROUP BY SESSION_ID, STUDENT_ID, CAMPUS_ID, STUDENT_COUNT,STUDENT_STARTING_SESSION_ID) AS Y) AS ZGROUP BY SESSION_ID, CAMPUS_ID
View 3 Replies
View Related
Jan 23, 2001
Hello
This is my 1st attempt at designing a database, and I have not finished
it completely, but I would like somebody to review and critique for me.
I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.
Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.
take care,
~Iris~
:)
View 1 Replies
View Related
Aug 31, 2007
All:
This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.
I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.
Thanks!
View 7 Replies
View Related
Mar 25, 2006
How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?
For example:
I have 2 tables, tbl1 and tbl2.
tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id
If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?
mradlmaier
View 3 Replies
View Related
Oct 14, 2015
I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.
Operation (Commodity, OperationKey) ==========>
Commodity (Commodity)
/
/
|
|
Advice (OperationKey) ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)
I have the following measure:
Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
View 9 Replies
View Related
Jul 20, 2005
Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh
View 3 Replies
View Related
Feb 4, 2007
How can I set a one-to-one relationship using the Management Studio Express and SQL Server 2005 Express
tblClient, CleintID (PK)
tblProcess, ClientID (FK)
View 5 Replies
View Related
Feb 22, 2007
Hello
I have need to write a query that I can pass in a bunch of filter criteria, and return 1 result....it's just ALL of the criteria must be matched and a row returned:
example:
Transaction table: id, reference
attribute table: attributeid, attribute
transactionAttribute: attributeid, transactionid
Example dat
Attribute table contains: 1 Red, 2 Blue, 3 Green
Transaction table contains: 1 one, 2 two, 3 three
transactionAttribute contains: (1,1), (1,2), (1,3), (2,3), (3,1)
If I pass in Red, Blue, Green - I need to be returned "one" only
If I pass in Red - I need to be returned "three" only
If I pass in Red, Green - nothing should be returned as it doesn't EXACTLY match the filter criteria
If anyone's able to help that would be wonderful!
Thanks, Paul
View 1 Replies
View Related
Aug 3, 2007
How to create a relation between gf_game and gf_gamegenre here? gf_gamegenre is responsible for the relation between a game and it's genre(s). The relationship between gf_genre and gf_gamegenre worked. (http://img361.imageshack.us/my.php?image=relationzl9.jpg)
When I try to set a relationshop between gamegenre and game I'm getting this error:
'gf_game' table saved successfully'gf_gamegenre' table- Unable to create relationship 'FK_gf_gamegenre_gf_game'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_gf_gamegenre_gf_game". The conflict occurred in database "gamefactor", table "dbo.gf_game", column 'gameID'.
Thanks for any help!
View 1 Replies
View Related
Aug 12, 2002
Can anyone provided insight on how to create a one-to-one relationship between
SQL tables? Every time I try to link two tables that should be one-to-one, the link says one-to-many.
How can I specify one-to-one when SQL Server automatically thinks it is a one-to-many?
Thanks,
Kellie
View 2 Replies
View Related
May 22, 2006
hi all,
how to implement the many to many relation ship....
i get stucked it.......
plz help
thanx
sajjad
View 3 Replies
View Related
Jul 18, 2006
Hey,
I know I'm asking a stupid question but I need to get a clear response please:
why using One-to-One relationship instead of meging the 2 tables in only one?
thanks.
View 3 Replies
View Related
May 19, 2007
Hi,
do you use ISA relationship or do you avoid it? Why?
Could you write me some its benefitsand drowback?
Thanks
View 11 Replies
View Related
Aug 30, 2005
Hey,
is it possible to get the relationship of a table via sql? Like I need to know which relations the table t has. Is that possible?
Thanks
View 5 Replies
View Related
Jul 23, 2005
I created 2 tables with one to one relationship. if I add a record intable A, how does table B record get created? Does SQL do thisautomatically because it is one to one relationship? or do I need tocreate a trigger? if i need a trigger, how do I get the ID of newrecord to create the same ID in table B?thanks for any help.Joe Klein
View 7 Replies
View Related
Oct 10, 2005
Hi,Do you guys know what's wrong with a one-to-one relationship?The reason I want to make it like this is that at the very end of the chain,the set of keys is huge. I want to limit the number of columns to be thekey. i.e. the [company] table has 1 column as the key. The [employee]table will have 2 columns as the key.e,g,If I add a [sale] table to the [company]-[employee] relationship, the thirdtablewill have 3 columns as the key -- "company id", "employee id", and "saleid".(e.g.)I have a company with many employees and computers. But instead of classifyall these, I just want to call all these as an entity. A company is anentity. An employee is just another entity. etc.So, instead of a one-to-many:[company]---*[employee]---*[sale]||*[computer]I make it one-to-one.[entity]---*[entity]If I want to know the name and address of the entity "employee", I will havea 1-to-1 table [employee] to look up the information for this employeeentity.[entity]---*[entity]||[company]||[employee]||[computer]||[sale]--[color=blue]> There is no answer.> There has not been an answer.> There will not be an answer.> That IS the answer!> And I am screwed.> Deadline was due yesterday.>> There is no point to life.> THAT IS THE POINT.> And we are screwed.> We will run out of oil soon.[/color]
View 13 Replies
View Related
Feb 14, 2006
How do I create a one to one relationship in a SQL2005 Express database? The foreign key needs to be the same as the primary key so it can't just increment to the next number.
View 6 Replies
View Related
Nov 17, 2007
Hi.
I get this error when i try to create a relationship in a db diagram (sql 2005)
"'tblActivedir' table saved successfully
'tblClient' table
- Unable to create relationship 'FK_tblClient_tblActivedir1'.
Introducing FOREIGN KEY constraint 'FK_tblClient_tblActivedir1' on table 'tblClient' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors."
What i have is 2 tables.
1 named client
1 named activedir
In the client table the columns i want to bind with activedirtable are FR1 and DC1
I want to bind them in the ID of the activedir table (both, in different fk relationships) so that they get the id of activedir.
Fr1 has an fk relationship with activedir (pk is activedir' id)
and DC1 exactly the same in another fk.
So i want both columns to comunicate with activedir.
If p.e. activedir has 3 elements (a,b,c) when i delete element a then werever FR1 or DC1 have this element(binded to it's id) then the element will also be deleted (id of the element) from both FR1 and DC1
I don't want to set Delete and Update action to none because i want the element changed or deleted from activedir, to do the same on Fr1 or DC1 or both.
Any help?
Thanks.
View 7 Replies
View Related
Nov 2, 2006
I am trying to create a 1:1 relationship, but not primary key to primary key. In table 1 I have a uniqueidentifier as a primary key. In table 2 I have an int as the primary key and a column that takes the uniqueidentifier from table 1. Everytime I drag and drop the relationship line and link table 1 to table 2 it creates a 1:N relationship: ie. tbl1.primarykey links to tbl2.column2. So I'm not linking primary key to primary key however I still want a 1:1 relationship.
How do I do that?
Thanks in advance.
View 3 Replies
View Related
Jan 24, 2008
SQLServer 2005 - I have two tables. One has a field defined as a Primary Unique Key. The other table has the same field, but the Index is defined as non-Unique, non-clustered. There is no primary key defined on the second table. I want to set up a one-to-many relationship between the two, but am not allowed.
This should be simple. What am I doing incorrectly?
View 8 Replies
View Related
Apr 22, 2007
This is the message that i get when trying to assign keys when creating diagrams in visual express:
'tbh_Polls' table saved successfully
'tbh_PollOptions' table
- Unable to create relationship 'FK_tbh_PollOptions_tbh_Polls'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tbh_PollOptions_tbh_Polls". The conflict occurred in database "C:USERSSTICKERDOCUMENTSMY WEB SITESPERCSHARPAPP_DATAASPNETDB.MDF", table "dbo.tbh_Polls", column 'PollID'.
PollID is my primary key in tbh_Polls
And PollID is in tbh_PollOptions table
No matter what I do, I get this message, I'm Lost!
Help plz.
Thanks
View 5 Replies
View Related
May 19, 2008
I have a fact table with 2 fields : "Dim Code 1" and "Dim Code 2" that I want to link with a Dim table.
I don't want to create two dimensions Dim1 and Dim2 but only one dimension with something like :
Code Snippet
Dim.Code=Fact.DimCode1 OR Dim.Code=Fact.DimCode2
Is it possible to do this ?
Thanks in advance
View 9 Replies
View Related
Nov 23, 2007
Hi.
(Sorry if i am not posting this in the right forum)
well i am learning to create a small software and have come up with four tables which are as follows;
Client
date
<!--[if !vml]--><!--[endif]-->client_id
name
address
contact_num
Smoking
smoking_id
client_id
meas_arm
meas_neck
meas_shoulder
Color_code
description
Trousers
trousers_id
client_id
meas_leg
meas_hips
meas_waist
color_code
description
Shirt
shirt_id
client_id
meas_arm
meas_neck
meas_shoulder
color_code
description
My question
Is it possible to have a relationship linking one single table to other several one.
For example i wanted to relate the field client_id from table client which is the primary to tables shirt,trousers and smoking with the client_id field which is the foreign key ?
thanks
View 8 Replies
View Related
Nov 22, 2006
Hi there everyone, this is my first post so go easy on me :)
Basically I am trying to get my database to copy the value in the UserId (unique identifier field) from the aspnet_Users table to a foreign key UserId in a table called userclassset. I have made this field the same datatype and created a relationship between the two. Unfortunately, when I add a user using the ASP.Net configuration tool it does not automatically copy this value into my own custom table. I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great!
Thanks :)
View 2 Replies
View Related
Dec 18, 2006
Hi, how can i make optional relationship?
for example: In table A, there is column 1, column 2, column3. In table B, there is column 4, column 5 and column 6.
column 1 and column 2 are primary keys for table A and table B. The relationships between table A and table B are column 2 and column 5; column3 and column 6. but optional (ie. when data exists in column 2, then column3 is null)
how can i set the relationship? because one of the columns data is null each time, error always occurs.
View 6 Replies
View Related
Apr 5, 2007
Hello, I created some SQL 2005 tables using Microsoft SQL Server Management Studio. I need to get the script code of those tables. I was able to do that by right clicking over each table. But how can I get the code for the relationships between the tables? Can't I create relationships between two tables by using T-SQL? Thanks, Miguel
View 3 Replies
View Related
Jul 3, 2007
When I try to insert a record on my DB (SQL 2005 Express) I get a constraint error. This is my table setup which has been simplified to expose the problem I have:
Categories TABLEint CatId PKvarchar CatName :
Items TABLEint ItemId PKvarchar ItemName :
X_Items_Categoriesint CatIdint ItemId
So basically I have a one-to-many relationship between Items and Categories, in other words each item is associated to one or more categories and this association is done via the X_Items_Categories cross table. On this cross table I set two constraints:
The CatId of each entry in the cross table (X_Items_Categories) must exist in the Category table, and
View 4 Replies
View Related
Oct 4, 2007
Hi there, I just set up my AppData and i'm trying to connect the Membership to a tbl_Profile that i created.I notice there's an application_id and a user_id which are uniqueidentifiers. I've been trying create a relationship to tbl_Profile's user_id column but it won't let me saying it's not compatible. Am i suppose to set tbl_Profile's user_id column to int or uniqueidentifier? I've tried both and it won't let me... THanks!
View 3 Replies
View Related
Dec 20, 2007
hi all geeks,
I have a problem regarding joins.
I have 2 tables,
Customer ( Customer_code, Agent1, Agent2, Agent3)
Agent( Agent_Code, Agent_name)
and data is like this:
Customer_code
Agent1
Agent2
Agent3
1
1
2
3
2
2
3
4
3
1
4
3
Agent Table
Agent_Code
Agent_name
1
X
2
Y
3
Z
4
P
5
Q
Now I want to retrieve all customer_code with their corresponding agent names, like 1 X Y Z2 Y Z P3 X P Z, any suggestions please,thanks a lot.
View 3 Replies
View Related
Jan 7, 2008
HiI have 2 tables one is called QuickLinks and another called QuickLinkItemsQuickLinksQuickLinkID<PK> UserID QuickLinkName-------------------------- ----------- ------------------------1 111-111-111-111 QuickLink12 111-111-111-111 QuickLink23 222-222-222-222 QuickLink1 QuickLinkItemsQuickLinkID <FK> CharacterName CharacterImagePath---------------------- ------------------------------- ---------------------------------------- 1 a path11 b path22 d path32 e path43 a path14 b path 2 So thats how I see the data will look like in both of these tables the problem however I don't know how to write this into Ado.net code. Like I have made a PK to FK relationship on QuickLinkID(through the relationship builder thing). I am just not sure how to I do this. For the sake of an example say you have 5 text boxes 2 text boxes for inserting the character names and 2 text boxes for inserting the CharacterImagePath. Then one for the Name of the QuickLink. So how would this be done? Like if it was one table it would be just some Insert commands. Like if this was all one table I would first get the userID from the asp membership table then grab all the values from the textBoxes and insert them where they go to and the QuickLinkID would get filled automatically. The thing that throws me off is the QuickLinkID since it needs to be the same ID as in the QuickLink table. So do I have to first insert some values into the QuickLink table then do a select on it and grab the ID then finally continue with the inserts Into the QuickLinkItems table? Or do I have to join these tables together?Thanks
View 18 Replies
View Related
