December 1, 2004 at 10:07 am
Is it possible to create relationships between tables of a database with T-SQL. I have looked at BOL and can not find anything that shows how to do this if it is possible.
Thanks.
December 1, 2004 at 11:10 am
Look under ALTER TABLE in BOL for help.
December 1, 2004 at 1:05 pm
Beth
What exactly do you mean by creating relationships between tables in T-SQL ?
When you write any T-SQL you can relate tables together using join syntax - post what you would like to do and we can see if we can help.
There are 10 types of people in this world - those that understand binary and those that don't
December 2, 2004 at 6:45 am
Hi, all
Thanks for responding to my question.
I work with SQL Server through SQL-Pass through from VFP. I create the database and tables this way. They want me to set relationships in the database next. The database is build and filled on the fly, so I can not open anything in Enterprise Manager or such. They want the relationships between tables to be there, because the user will not know the relationships, and I will not be in charge of the reports on that database. We have another database, VFP, but some users do not want to use it, so I need to see about putting the relations in the database instead of the application.
Hope I gave enough info.
Beth
December 2, 2004 at 7:50 am
As was mentioned earlier, refer to CREATE TABLE and ALTER TABLE in Books Online. Specifically, you can define foreign keys to show table relationships.
ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { [ WITH CHECK | WITH NOCHECK ] CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}
< column_definition > ::=
{ column_name data_type }
[ [ DEFAULT constant_expression ] [ WITH VALUES ]
| [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ]
]
[ ROWGUIDCOL ]
[ COLLATE < collation_name > ]
[ < column_constraint > ] [ ...n ]
< column_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
| [ [ FOREIGN KEY ]
REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
]
| CHECK [ NOT FOR REPLICATION ]
( logical_expression )
}
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON {filegroup | DEFAULT } ]
]
| FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression
[ FOR column ] [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
}
December 2, 2004 at 8:58 am
Thanks for the info.
I will give this a try.
December 2, 2004 at 10:07 am
Relationships are a true tool of the Jedi.
Not to be taken lightly! Created once, they must be. Not altered or maintained day to day. Set in stone! Such is the way of the relational database.
When creating a table you are, be clear in your intentions:
CREATE TABLE ( ... SomeCol REFERENCES sometimes (someothercol))
With ALTER TABLE, avoid it you must! It leads to the dark side. Trust in your feelings; if the design pleases you not, start over you must!
Existing data will not heed the rules you newly specify!
December 2, 2004 at 12:25 pm
What if the data is already checked in the main database, which I have no control over, and then dumped into my two databases, VFP and SQL server? The tables are built at one time (on the fly) and that is when the relationship will be put in. At the moment, I do not add any indexes to the tables until after a the data is imported into the database because of speed issues. The indexes are then added.
After that the data is updated on the fly, record by record. The database will be rebuilt at different times by request of the user.
Hope this makes sence.
Beth
P.S. did you notice that Yoda did not talk like that in Attack of the Clones?
December 2, 2004 at 2:43 pm
hm.. reporting database? referential integrity, it may not need. As long as source is valid.
You say rebuilt -- with what method you do use? Drop and recreate all tables and objects? Or, empty and re-fill existing tables? Second option, Yoda prefers. Structure should be a constant, hm? Change it not, based on data.
Prefered option -- emptying and re-filling tables -- will allow for RI, yes? Order of inserts, becomes important it does.
December 3, 2004 at 7:10 am
If source not valid, database going to be bad anyway.
Rebuild is drop and recreate.
My reporting database should not have to worry about RI because other application that controls true DB does that. If true DB bad, we need to see it in the reporting DB also.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply