SQL Database diagram - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Database diagram

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-25 : 08:34:56

Hello,
would it be possible to replicate the same table with different name in Database diagram?

i added a table T1 into diagram i need the same table with different name as it cant be rewrite with same name.

Any ideas would be much appreciate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-25 : 09:16:08

hmm? whats the purpose of duplicating the same table in diagram?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-25 : 10:53:12

Hi Visakh,

These are my joins i would like to create a database diagram for based on these joins.
schedule and reference tables need to duplicated,would it be possible?

FROM SCHEDULES AS SCP
INNER JOIN SCHEDULES AS SCC ON SCP.SCHDL_REFNO = SCC.PARNT_REFNO
INNER JOIN REFERENCE_VALUES AS ATTND ON SCP.ATTND_REFNO = ATTND.RFVAL_REFNO
INNER JOIN PROF_CARERS AS HCPs ON SCP.PROCA_REFNO = HCPs.PROCA_REFNO
LEFT JOIN SPECIALTIES ON SCP.SPECT_REFNO = SPECIALTIES.SPECT_REFNO
INNER JOIN REFERENCE_VALUES AS LOTYP ON SCP.LOTYP_REFNO = LOTYP.RFVAL_REFNO
INNER JOIN REFERENCE_VALUES AS CONTY ON SCP.CONTY_REFNO = CONTY.RFVAL_REFNO
INNER JOIN REFERRALS ON SCP.REFRL_REFNO = REFERRALS.REFRL_REFNO
INNER JOIN PATIENTS ON SCP.PATNT_REFNO = PATIENTS.PATNT_REFNO
INNER JOIN REFERENCE_VALUES AS SEXXX ON PATIENTS.SEXXX_REFNO = SEXXX.RFVAL_REFNO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-26 : 10:10:00

quote:
Originally posted by satya068

Hi Visakh,

These are my joins i would like to create a database diagram for based on these joins.
schedule and reference tables need to duplicated,would it be possible?

FROM SCHEDULES AS SCP
INNER JOIN SCHEDULES AS SCC ON SCP.SCHDL_REFNO = SCC.PARNT_REFNO
INNER JOIN REFERENCE_VALUES AS ATTND ON SCP.ATTND_REFNO = ATTND.RFVAL_REFNO
INNER JOIN PROF_CARERS AS HCPs ON SCP.PROCA_REFNO = HCPs.PROCA_REFNO
LEFT JOIN SPECIALTIES ON SCP.SPECT_REFNO = SPECIALTIES.SPECT_REFNO
INNER JOIN REFERENCE_VALUES AS LOTYP ON SCP.LOTYP_REFNO = LOTYP.RFVAL_REFNO
INNER JOIN REFERENCE_VALUES AS CONTY ON SCP.CONTY_REFNO = CONTY.RFVAL_REFNO
INNER JOIN REFERRALS ON SCP.REFRL_REFNO = REFERRALS.REFRL_REFNO
INNER JOIN PATIENTS ON SCP.PATNT_REFNO = PATIENTS.PATNT_REFNO
INNER JOIN REFERENCE_VALUES AS SEXXX ON PATIENTS.SEXXX_REFNO = SEXXX.RFVAL_REFNO

for that you just need multiple relations to same table, not duplicating table itself in db diagram

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-28 : 03:52:09

Hi Visakh

can you tell me how to use multiple function in DB diagram for the below tables, primary key for REFERENCE_VALUES IS RFVAL_REFNO and SP is SCHDL_REFNO.
INNER JOIN REFERENCE_VALUES AS ATTND ON SCP.ATTND_REFNO = ATTND.RFVAL_REFNO
INNER JOIN REFERENCE_VALUES AS LOTYP ON SCP.LOTYP_REFNO = LOTYP.RFVAL_REFNO
INNER JOIN REFERENCE_VALUES AS CONTY ON SCP.CONTY_REFNO = CONTY.RFVAL_REFNO

can you tell me how to use multiple function in DB diagram for the above tables primary key for REFERENCE_VALUES IS RFVAL_REFNO and SP is SCHDL_REFNO.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 04:50:16

you can add relationships based on the joining columns in database diagram

http://www.mssqltips.com/sqlservertip/1816/getting-started-with-sql-server-database-diagrams/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-28 : 08:11:28

Hi Visakh,
Thanks for providing me with required infor site.

when mapping the relation with promary key PATNT_REFNO IN PATIENTS and PATNT_REFNO as foreign key in Schedules when saving the diagram ,iam ending with below error message...

'PATIENTS' table saved successfully
'SCHEDULES' table
- Unable to create relationship 'FK_SCHEDULES_PATIENTS'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SCHEDULES_PATIENTS". The conflict occurred in database "PIMS_LOAD_TEST", table "dbo.PATIENTS", column 'PATNT_REFNO'.

when mapping the relation with promary key PATNT_REFNO IN PATIENTS and PATNT_REFNO as foreign key in Schedules

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 08:16:32

quote:
Originally posted by satya068

Hi Visakh,
Thanks for providing me with required infor site.

when mapping the relation with promary key PATNT_REFNO IN PATIENTS and PATNT_REFNO as foreign key in Schedules when saving the diagram ,iam ending with below error message...

'PATIENTS' table saved successfully
'SCHEDULES' table
- Unable to create relationship 'FK_SCHEDULES_PATIENTS'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SCHEDULES_PATIENTS". The conflict occurred in database "PIMS_LOAD_TEST", table "dbo.PATIENTS", column 'PATNT_REFNO'.

when mapping the relation with promary key PATNT_REFNO IN PATIENTS and PATNT_REFNO as foreign key in Schedules

that means yu've some data in your table PATIENTS which is not pointing to a valid data in SCEHDULES. in such cases you cant create a relationship unless you make sure data is in sync

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-28 : 08:39:29

INNER JOIN PATIENTS ON SCP.PATNT_REFNO = PATIENTS.PATNT_REFNO

for the above sql script join condition, i am able to get data satisfing the ablove condition then why the relationshipn between those tables are not sync..in db diagram.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 09:00:27

quote:
Originally posted by satya068

INNER JOIN PATIENTS ON SCP.PATNT_REFNO = PATIENTS.PATNT_REFNO

for the above sql script join condition, i am able to get data satisfing the ablove condition then why the relationshipn between those tables are not sync..in db diagram.

nope there are some data not returned by this which is violating relationship check below query result


SELECT *
FROM PATIENTS
LEFT JOIN SCHEDULES AS SCP
ON SCP.PATNT_REFNO = PATIENTS.PATNT_REFNO
WHERE SCP.PATNT_REFNO  IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources