Installed MS sql 2012. Working on a class assignment.
Used the MS SQL server management tool to create a database named CS660. Then used scripts to create some tables. The tables can be seen via the gui and have the correct columns in them. My problem is trying to create foreign keys. I get an error that the key references an invalid table.
here is the code.
CREATE TABLE PRODUCTS
( ID INT NOT NULL,
CATEGORY VARCHAR(12) NOT NULL,
REORDER INT NOT NULL,
SIZE VARCHAR(12) ,
DISCRIPTION TEXT NOT NULL,
PRICE SMALLMONEY NOT NULL,
QNTY INT,
FROMSUPPLIER INT REFERENCES SUPPLIERS(ID) NOT NULL,
FROMBACKUP INT REFERENCES SUPPLIERS(ID) NOT Null,
PRIMARY KEY (ID));
I have the ID setup as a primary key. No other constraints setup yet in the suppliers table.
On the different database that is possible, since I have never worked with ms sql before, but I don't think so. In the GUI I can try to create a query using the tools, and the gui will show an error that it can't find the table, but it will run without errors
There is a PULL DOWN for the database name, so suggest double check that you are "in" the right one, but from what you have said it seems unlikely that's the cause.
Copy & Pasting the exact error message, verbatim, may help us to spot what is wrong.
This is very frustrating. Still have the error in the editor, but I finally tried the code and it works correctly. So I guess I was doing most of it right in the first place. Any idea why the editor would say the table was not found, but the code runs without any issues?
Ah ... that's the salient bit. I don't know why it would complain (and I don't make enough use of IntelliSense to know how to advise you to improve / fix it), but the red-wiggly-line is based on some Meta Data which SQL uses to try to spot typing mistakes, in real time, and my guess is that either a) the metadata is stale / incomplete, for some reason, or b) it just isn't possible to resolve at that point or c) MS haven't made Intellisense work for that particular situation.
is just possible that putting a space between TableName and "(" might cheer it up - but if so that's a bit pants!
Of course, now I look at it through a beginner's eyes, I can see how useful it WOULD be for that red-wiggly-line to have no false-errors.
I now also see what you mean about running it and getting no errors ... definitely nothing to worry about, in this case, as your code is indeed "clean".
I use the GUI Table Designer, set up all the columns, indexes, FKeys etc. that I want but then I do NOT press "Save" but instead use the "Create Script" button. SQL then generates the script for me - which I can check / modify or just use as the basis for multiple snippets of DDL change that will form part of my release / upgrade script.
By the by, I recommend not using the "TEXT" datatype as it has been deprecated for some time. The replacement is VARCHAR(MAX) - which has a lot more flexibility than the old TEXT datatype.
Thank you for the advice. This is a database class I'm taking and we have been asked to use MS SQL server. I'm more used to mysql from the command line..... But intellisense saves so much time when it works correctly. It took me 24+ hours to figure this out. I never tried to run the script for quite a while because of the "error"
Part of the problem is that you did not schema-qualify the table. If the default schema for your login is not 'dbo' - and is not owned by 'dbo' then the meta-data lookup would be trying to find that table in your default schema.
You should schema-qualify every object - all the time. That is...
CREATE TABLE dbo.MyTable ...
Instead of
CREATE TABLE MyTable ...
In the former, you know the schema being used to create the table - you can guarantee it is the correct schema regardless of default schema for the logged in user. In the latter...it will use the default schema of the logged in user - which may or may not be the correct schema.
Wow, I'm often still amazed at how awful the table "designs" in classes are. If they don't offer you a data modeling class -- or based on this, even if they do -- be sure to look online into logical data modeling and entity definitions. When designing tables, you should first do a logical data model, then design physical tables based on the logical model (and only from a logical model).
For the current table, "ID" should be PRODUCT_ID; CATEGORY should be encoded into a smallint value;""SIZE" should be a encoded as well, likely a tinyint; "DISCRIPTION" should use VARCHAR(MAX) NOT "TEXT" (text has been obsolete for years); "QNTY" should be "QUANTITY" or even QTY; the FROM values should be in a separate table, since there's more than one of them. And the PK and FKs should be explicitly named not allowed to default to a computer-generated name.
We skimmed over the data modeling part of our book, which did not seem very extensive. Thank you everybody. I am going to go in and do new creates with the schema and see if that fixes all the intellisense errors.
I sit on the fence on that when there are exactly two (rather than "multiple"). Its just a pain to have to join the child table, select a specific row (e.g. to get the From Supplier), and then make the JOIN to supplier table.
Do you do that too? or would you always use a child table?
I agree that the suppliers should be setup with more than two most of the time. I based this off of a little shop that I had and I normally had a primary supplier and a backup, so that is what I did the tables off of. But I know if the business grows there can be more than two supplier for ever product so having a table that covered that would be the best way to do it.
My question to Client is "Can you guarantee that you will never have more than 2?" ... the answer is always (albeit sometimes after I push them a bit!) "No"
What about a temporary issue with a supplier? A hurricane in their town, and you'd need to switch suppliers temporarily. I wouldn't want to overwrite my normal supplier info, and thus lose that data, but I also can't use them for, say, a week, until they get back going again.
