SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

glompix: Jan 19, 2004; propane grill-pilled

Here's another SQL Server 2005 performance-tuning question.

I'm exploring the profiler, and I've been seeing some really weird poo poo. We use ADO.NET to talk to SQL in a WinForms application. I started the profiler up on our production server. Something tells me this is a bad idea to begin with, but I only set it to run for 1 minute. We have probably 25-40 users at peak, (3-5pm) and around 2/3 to 3/4 of those users have a grid open that refreshes itself every five minutes.

The query that drives this grid is showing up on the profiler WAYYYYYY too much. Like, one transaction is showing up thousands of times. Is this normal, or is it a cause for concern? Just as a note, this query really takes about 4 seconds to execute.

Click here for the full 1276x804 image.

# ? Feb 25, 2009 22:45

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 21:37

No Safe Word: Feb 26, 2005

Speaking of SQL Server performance tuning, a coworker sent me a link to Red Gate offering up their eBook "Mastering SQL Server Profiler" for free, enjoy:

http://www.red-gate.com/products/SQL_Response/offers/Mastering Profiler eBook.pdf

# ? Feb 25, 2009 23:20

MrMoo: Sep 14, 2000

moostaffa posted:

I currently have all this data in one table (No expensive queries will be needed on this, only lookups on the single index), would it give any performance boost to split this table into, say, 10 or 100 smaller tables?

On high end databases no, it will make it worse. The database' job is to manage indexes and large tables. However I think only Oracle is good at this. Sybase used to be terrible unless things have changed.

# ? Feb 26, 2009 02:48

grrowl: Jan 17, 2007; I LOVE 4CHAN YIFF YIFF YIFF

I'm hoping there's a hidden way to do this that i've just failed to come across yet, but my problem at the moment is this: In my table i have both `datecreated` and `dateupdated` columns. When we create a row we set `datecreated`, and when a row is updated we set `dateupdated`. Now, I want to display the rows ordered by `dateupdated` or `datecreated`, whichever is higher (or NOT NULL) for each row.

I'm hoping something like this would be possbile:
> SELECT `title`, `datecreated`, `dateupdated` FROM `threads` ORDER BY MAX(`datecreated`, `dateupdated`) DESC

but, of course, MAX doesn't work that way and I can't find anything that does without trying to wrangle with CASE (since i have no experience with it and it looks like overkill). Or should I just throw in the towel and set `dateupdated` when I create a row?

Thanks guys.

# ? Feb 26, 2009 06:04

Bad Titty Puker: Nov 3, 2007; Soiled Meat

grrowl posted:

I'm hoping there's a hidden way to do this that i've just failed to come across yet, but my problem at the moment is this: In my table i have both `datecreated` and `dateupdated` columns. When we create a row we set `datecreated`, and when a row is updated we set `dateupdated`. Now, I want to display the rows ordered by `dateupdated` or `datecreated`, whichever is higher (or NOT NULL) for each row.

I'm hoping something like this would be possbile:
> SELECT `title`, `datecreated`, `dateupdated` FROM `threads` ORDER BY MAX(`datecreated`, `dateupdated`) DESC

but, of course, MAX doesn't work that way and I can't find anything that does without trying to wrangle with CASE (since i have no experience with it and it looks like overkill). Or should I just throw in the towel and set `dateupdated` when I create a row?

Thanks guys.

Something like this (untested)?

code:

SELECT
  title, 
  datecreated, 
  dateupdated,
  CASE
    WHEN COALESCE(datecreated, '1900-01-01') > COALESCE(dateupdated, '1900-01-01')
    THEN datecreated
  ELSE
    dateupdated
  END AS sortcol
FROM
  threads
ORDER BY
  sortcol

# ? Feb 26, 2009 06:14

grrowl: Jan 17, 2007; I LOVE 4CHAN YIFF YIFF YIFF

camels posted:

Something like this (untested)?

I went with WHEN dateupdated IS NULL THEN datecreated, and it worked like a charm! Thanks for your help

# ? Feb 26, 2009 06:33

Bad Titty Puker: Nov 3, 2007; Soiled Meat

grrowl posted:

I went with WHEN dateupdated IS NULL THEN datecreated, and it worked like a charm! Thanks for your help

Oh, then you can just use COALESCE -

code:

SELECT
  title, 
  datecreated, 
  dateupdated,
  COALESCE(dateupdated, datecreated) AS sortcol
FROM
  threads
ORDER BY
  sortcol

# ? Feb 26, 2009 06:48

uXs: May 3, 2005; Mark it zero!

Why does the following work ?

Table tblTarget:

code:

idTarget value	
1        NULL
2        NULL

Table tblSource:

code:

idSource idTarget value	
1        1        a
2        1        b
3        2        c
4        2        d

Update statement:

code:

update tblTarget
set tblTarget.value = tblSource.value
from tblTarget
inner join tblSource on tblTarget.idTarget = tblSource.idTarget

Why does this work? Shouldn't this give an "ambiguous update rows" error or something like that? I could swear I remember SQL Server not allowing this kind of ambiguous update statement, but now it just seems to "work". ("work" in quotes because you have no way of knowing what the results are going to be.)

# ? Feb 26, 2009 17:27

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

uXs posted:

Why does this work? Shouldn't this give an "ambiguous update rows" error or something like that? I could swear I remember SQL Server not allowing this kind of ambiguous update statement, but now it just seems to "work". ("work" in quotes because you have no way of knowing what the results are going to be.)

from SQL Server 2005 Books Online

Microsoft posted:

The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

Ultimately I think they let you do it because it's probably more trouble than it's worth to try and detect such a problem beforehand. You can do it, but the results are undefined, so do it at your own risk.

# ? Feb 26, 2009 17:48

uXs: May 3, 2005; Mark it zero!

Jethro posted:

from SQL Server 2005 Books Online
Ultimately I think they let you do it because it's probably more trouble than it's worth to try and detect such a problem beforehand. You can do it, but the results are undefined, so do it at your own risk.

I could swear I have tried such statements before, but SQL Server didn't allow it.

Problem is when you don't realize that you're going to get an undefined result. I'd prefer getting an error message instead of the server just giving you a result randomly.

I discovered it now for the problem I'm working on, but what if I hadn't ? That would suck.

# ? Feb 26, 2009 18:12

permabanned Arf: May 29, 2001; by Ozma

I am super retarded with SQL. I feel like when I get something right its more by dumb luck. It's like I'm so close to understanding my own queries but yet so far.

Okay so I'm working with two tables on this query, a one-to-many relationship between a group table and individual table. Each individual has a group ID as a foreign key, and each group has a group leader (individual ID) as a foreign key.

I have to construct a query that pulls all the individual details sorted by group, and for each individual also list all the stored details of their assigned group leader.

code:

SELECT   group_tbl.grp_id, 
         grp_name, 
         ind_id, 
         ind_surname, 
         ind_forename, 
         grp_leader_id 
FROM     group_tbl, 
         ind_tbl 
WHERE    group_tbl.grp_id = ind_tbl.grp_id 
ORDER BY group_tbl.grp_id, 
         ind_surname, 
         ind_forename DESC;

Can someone point me in the direction of the logic I'll need to create the second join between the grp_leader_id and the ind_id which will allow me to return the corresponding data on the same row as the individual details? I've come to a brick wall in my knowledge and have no idea what I should do.

# ? Feb 26, 2009 18:44

Zoracle Zed: Jul 10, 2001

Arf posted:

Can someone point me in the direction of the logic I'll need to create the second join between the grp_leader_id and the ind_id which will allow me to return the corresponding data on the same row as the individual details? I've come to a brick wall in my knowledge and have no idea what I should do.

I think the thing you're missing is: You've got two tables: individuals and groups. You're interested in three sets: individuals, groups, and group leaders. That means you need three tables.

code:

SELECT  ...
FROM    ind_tbl AS individuals, 
        group_tbl AS groups,
        ind_tbl AS groupleaders
WHERE   groups.grp_id = individuals.grp_id
        AND

Can you see the missing join condition now?

# ? Feb 26, 2009 19:13

permabanned Arf: May 29, 2001; by Ozma

Thanks mate, thats a massive help and makes sense. I'll go implement it and see how it goes.

# ? Feb 26, 2009 20:09

Morpheus: Apr 18, 2008; My favourite little monsters

I have an Oracle question! It's been bugging me for a while now:

I need to get the smallest number from one column from a group of selected rows. What do I mean, you ask? Well, let's say I pull a query that results in this:

code:

select ID, NAME, CATEGORY from TABLENAME where CATEGORY=10;
ID | NAME   | CATEGORY
----------------------
28 | Entry1 | 10
27 | Entry2 | 10
30 | Entry3 | 10
29 | Entry4 | 10

I need to be able to get only Entry1. Now, the table I'm using has a bunch of columns, and the only thing that these given entries have in common is the CATEGORY column. Another four entries will have a different CATEGORY.

I know there's a command (LEAST) that gets the lowest number in a list of numbers, but it doesn't allow for SELECT statements, at least I don't think it does. There must be a way to do this; it just seems so simple to do...help would be appreciated.

Edit: Well gently caress I'm dumb. The answer is simply the following query:

code:

select ID, NAME, CATEGORY from TABLENAME where CATEGORY=10 && ID=(select min(ID) from TABLENAME where CATEGORY=10);

Morpheus fucked around with this message at 22:00 on Feb 26, 2009

# ? Feb 26, 2009 21:53

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Morpheus posted:

I have an Oracle question! It's been bugging me for a while now:

I need to get the smallest number from one column from a group of selected rows. What do I mean, you ask? Well, let's say I pull a query that results in this:
code:
select ID, NAME, CATEGORY from TABLENAME where CATEGORY=10;
ID | NAME   | CATEGORY
----------------------
28 | Entry1 | 10
27 | Entry2 | 10
30 | Entry3 | 10
29 | Entry4 | 10

Alternatively:

code:

select id,
       name,
       category
  from tablename
 where category=10
       and rownum = 1
order by id

EDIT: OOps. I guess I spend too much of my time in T-SQL where TOP N works like that.

Jethro fucked around with this message at 21:43 on Feb 27, 2009

# ? Feb 26, 2009 22:30

var1ety: Jul 26, 2004

Jethro posted:

Alternatively:

code:

select id,
       name,
       category
  from tablename
 where category=10
       and rownum = 1
order by id

Where clauses are applied before sorting, so that will not do what you expect.

code:

select id, name, category
  from (select id, name, category from tablename where category = 10 order by id)
 where rownum = 1

# ? Feb 27, 2009 01:07

Xae: Jan 19, 2005

If you want to mess around a bit you can try using Rank. It is handy to know and this is a good excuse as to learn.

# ? Feb 27, 2009 05:45

Morpheus: Apr 18, 2008; My favourite little monsters

drat I explained the question poorly. While I need to get Entry1, I also need to get Entry2 in a seperate query, Entry3 in a third query, and so forth. So while rownum is perfect for getting the first query, it doesn't quite work when it comes to getting the other entries (and only the other entries).

# ? Feb 27, 2009 20:59

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

In that case, do what Xae suggested and use Rank.

# ? Feb 27, 2009 21:28

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Shout out to Victor, if you want to continue our discussion in the thread as opposed to IRC. So I have an ETL insert statement that sometimes takes up to 6 hours (usually it ends in < 20 min). Part of the problem is that I can't seem to replicate the problem when I run the unit test locally. The problem happens only when the unit test server runs the test. I'm not even sure how that would affect the script.

The strategy you suggested was to trace it. But I'm worried that even if I do catch it in action, it will just tell me something that we could have already presumed: this statement is consuming a lot of time/resources. Duh. But why?

# ? Feb 27, 2009 21:53

Victor: Jun 18, 2004

How many statements, as in select/insert/update/delete, are involved? Have you looked at table fragmentation? How about indexes? If one of those is different, the cost of doing things could be radically different. Perhaps you could try comparing the PerfMon graphs of the two different servers doing their thing. Get an idea of whether one is doing a metric fuckton more I/O, CPU, TLOG, or something else. Find out what actually is different, other than the relatively useless "runtime" number.

# ? Feb 27, 2009 23:03

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Triple Tech posted:

Shout out to Victor, if you want to continue our discussion in the thread as opposed to IRC. So I have an ETL insert statement that sometimes takes up to 6 hours (usually it ends in < 20 min). Part of the problem is that I can't seem to replicate the problem when I run the unit test locally. The problem happens only when the unit test server runs the test. I'm not even sure how that would affect the script.

The strategy you suggested was to trace it. But I'm worried that even if I do catch it in action, it will just tell me something that we could have already presumed: this statement is consuming a lot of time/resources. Duh. But why?

See if you can get the actual query execution plan...

# ? Feb 27, 2009 23:11

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

camels posted:

See if you can get the actual query execution plan...

The query plan looks exactly like you think it would, with nothing weird going on. Joins, a select, an insert statement.

# ? Feb 27, 2009 23:53

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Triple Tech posted:

The query plan looks exactly like you think it would, with nothing weird going on. Joins, a select, an insert statement.

Is that the *actual* query plan? You would need to capture it in the SQL trace...

What RDBMS are you using? Sorry if I missed it.

Can you post the SQL you are running, and the DDL including indexes? Can you post the query plan? What _kind_ of joins? Seeks? Scans? Sorts?

# ? Feb 28, 2009 00:17

DTMTCM: Feb 18, 2005; I misclicked post icon so I have this account now.

What is the development process like for applications that utilize SQL Server 2005 (or production databases in general)? I'm most curious about design and development techniques and if there is a nice way to integrating schema changes into source control (such as VSS). Any good books or online tutorials to recommend?

# ? Feb 28, 2009 00:27

Victor: Jun 18, 2004

You can use DDL triggers to do auditing, but unfortunately object renames aren't supported until 2008; here are a few DDL trigger articles I wrote.

# ? Feb 28, 2009 02:50

DTMTCM: Feb 18, 2005; I misclicked post icon so I have this account now.

Victor posted:

You can use DDL triggers to do auditing, but unfortunately object renames aren't supported until 2008; here are a few DDL trigger articles I wrote.

So I guess for 2005, I'm going to have to SELECT INTO a new table (possibly twice to rename a column) if I need to do some renaming to use this scheme? The database isn't going to be horribly big so this would be feasible. I guess one alternative would be to log all the changes I make in SQL code and then apply them on the live server on release. I'll give you DDL triggers a shot and see if I can make it work.

The only reason why I asked such a vague question is because I was handed a project at work to "maintain" the application that handles the work flow for the company. The database I received consisted of one unnormalized table handling all the data. Management has requested a very iterative development schedule for the program with many new features and support for future unnamed features. There are about 20 other tables but they each have one field each which looks like the previous ~~developer~~ space occupier made an attempt at normalization on the live server. I assume this is going to result in many changes to the schema.

# ? Feb 28, 2009 06:27

beuges: Jul 4, 2005; fluffy bunny butterfly broomstick

I'm not so hot with databases but I can get by with simple stuf. I've got this little script that I wrote over a year ago to calculate and update ranks in a table. This used to work in my previous version of mysql (not sure of the exact version) but ever since I upgraded to 5.0.70 it doesn't work any more.

The script is as follows:

code:

dhiren@lolbox /var/www/dhiren.za.net/fred $ cat fredupdateranks.sql
UPDATE fred_users SET globalrank=IF(@rank,@rank:=@rank+1,@rank:=1) ORDER BY keystrokes DESC;
UPDATE fred_teams SET teamrank=IF(@teamrank,@teamrank:=@teamrank+1,@teamrank:=1) ORDER BY keystrokes DESC;

I have no idea how I came up with this script - I was either drunk or found it on the net somewhere. But it used to work, and now it doesn't - when it runs now (in an hourly cron job) all the ranks are set to 1. Clearly the functioning of the IF() function or the scope that the @rank variable has in the UPDATE statement has changed somewhere inbetween, and I was probably not using it correctly in the first place, but does anyone have any suggestions as to how to get it working again?

Thanks in advance!

beuges fucked around with this message at 08:47 on Feb 28, 2009

# ? Feb 28, 2009 08:16

Victor: Jun 18, 2004

urmomlolzinbed posted:

So I guess for 2005, I'm going to have to SELECT INTO a new table (possibly twice to rename a column) if I need to do some renaming to use this scheme?

Instead, I direct all renames through a sproc I wrote, bc_rename. Unfortunately, one cannot get SSMS to use this, but if you yourself do all the renames, you might try it. It even allows you to omit the @object_type most of the time (I think you need to specify it for indexes or something).

# ? Feb 28, 2009 08:38

DTMTCM: Feb 18, 2005; I misclicked post icon so I have this account now.

Victor posted:

Instead, I direct all renames through a sproc I wrote, bc_rename. Unfortunately, one cannot get SSMS to use this, but if you yourself do all the renames, you might try it. It even allows you to omit the @object_type most of the time (I think you need to specify it for indexes or something).

I completely misunderstood/misread your comment on that as "this only works on 2008". I'll see how this works out Monday. On a side note, I asked a manager where the old development database was and all I got was a hearty laugh (but communicated in a more professional manner).

# ? Feb 28, 2009 23:04

deathandtaxes: Jan 12, 2007; by I Ozma Myself

Edit: Solved, don't bother helping me

First off, I'm pretty new to SQL/MySQL so pardon me if this is retardedly obvious. I'm having a problem constructing a query for a MySQL database. I have a site that revolves around users voting on photos. Right now, it selects a random photo from the database for users to vote on. However, I want to change it so that it only selects photos that users haven't actually voted on. Obviously, saving the votes is trivial, but I'm having problems selecting new photos that a user hasn't voted on.

tl;dr version: Basically, what I want to do is select all the photos a given user has not voted for.

There are two tables, one with the photos, and another with all the votes. Here are abbreviated versions of the DESCRIBE output for each one.

Photos:

code:

+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| id       | varchar(32)      | NO   | PRI | NULL    |       |
+----------+------------------+------+-----+---------+-------+

Votes:

code:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | varchar(32) | NO   |     | NULL    |       |
| username | varchar(12) | NO   |     | NULL    |       |
| value    | varchar(5)  | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

Obviously the best way to do this would be with EXCEPT, something like

code:

(SELECT id FROM photo) EXCEPT (SELECT id FROM votes WHERE username = 'username')

However, the EXCEPT statement has not yet been implemented in MYSQL(http://bugs.mysql.com/bug.php?id=1309). I tried to do it using a LEFT JOIN but I couldn't get it to work. Any suggestions?

deathandtaxes fucked around with this message at 16:14 on Mar 1, 2009

# ? Mar 1, 2009 07:37

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Left join is the way to do it in MySQL. Post what you tried and maybe we can fix it for you.

# ? Mar 1, 2009 07:57

deathandtaxes: Jan 12, 2007; by I Ozma Myself

Jethro posted:

Left join is the way to do it in MySQL. Post what you tried and maybe we can fix it for you.

Actually, I crossposted on to the codeigniter forums, and someone there suggested I do it this way:

code:

SELECT photos.id
FROM photos
WHERE NOT EXISTS (
  SELECT id
  FROM votes
  WHERE
    votes.id = photos.id
    AND username = 'username'
)

Which has been working pretty well so far.

deathandtaxes fucked around with this message at 16:18 on Mar 1, 2009

# ? Mar 1, 2009 16:10

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

How should I go about (if I should) querying two databases at once? I just split out our internal user system from another database, but now I have queries that either need to look at both databases at once, or I'm going to have to do a lot of looping.

Edit: I guess it's simple, just prepend the database name, but now I'm going to have to make a single user who can view both databases...

Golbez fucked around with this message at 00:29 on Mar 3, 2009

# ? Mar 3, 2009 00:25

wolrah: May 8, 2006; what?

Hopefully simple MySQL question.

I have a query that results in two columns of results, a customer ID and a cost for whatever month I've specified in the query. What I'd like to do is run the same query over again for a different month and have that get inserted as another column. Some customers have no data in some months as they were added or removed from the system, so it'll need to fill those spots in the output with NULLs or zeros.

edit: the query I'm using to get one month of data

code:

SELECT `group`, SUM((cdr_new.chargeDuration / 60 * rates.rate)) AS 'cost'
FROM cdr_new
LEFT JOIN rates
ON `product_code-call` = rates.code
WHERE LEFT(`startTime`, 6) = '200902'
GROUP BY `group`

and a small subset of the returned data (obviously can't publish identifiable customer info):

code:

+-------------------------------+-------------------+
| group                         | cost              |
+-------------------------------+-------------------+
| Resi K                        |  7.41592000000002 | 
| Resi1                         |                 0 | 
| ResiA                         |           1.69735 | 
| ResiB                         |          25.33945 | 
| ResiC                         |           0.22568 | 
| ResiF                         |           0.97017 | 
| ResiH                         |            0.0312 | 
| ResiS                         |           4.58148 | 
| ResiW                         |            1.7869 |  
| _Testing and Demo_            |           0.49162 | 
| _Testing_                     |                 0 | 
+-------------------------------+-------------------+
208 rows in set (3 min 48.35 sec)

I will gladly admit that I'm a SQL retard (I used JOIN for the first time ever last night designing this query) and the cdr_new table is utter crap (it's a raw import of CSV files delivered from my softswitch with one index and no real typing, I'm working on replacing it with something using the appropriate types and more indices).

edit2: I just figured out how to treat SELECT results as a table in a JOIN, so I came up with this absolute beast of a query

http://pastebin.com/f6b48d991

I'm fairly certain I did about a million things wrong there and it's going to take nearly an hour to complete if it's fair to figure it'll take 14 times as long as the above query did. That said, I only need to run it once.

edit3: I hate 32 bit memory limits...

After an hour of running, the server returned and Sequel Pro (CocoaMySQL fork) shat itself. Restarting using the 64 bit CLI client and just having it dump to CSV.

edit4: I'm not sure what I did wrong there, but I managed to end up with over 1000 rows per customer in the output. I killed the job when I noticed the CSV was over 7GB, when it should be on the order of KB.

wolrah fucked around with this message at 15:29 on Mar 3, 2009

# ? Mar 3, 2009 02:33

Azerban: Oct 28, 2003

So I've got a schema that looks like this;

Student (sID: integer, sName: string, major: string, year: string)
Class (cName: string, meets_at: time, roomno: string, fID: integer)
Faculty (fID: integer, fName: string, deptID: integer)

with a relationship;

Enrolled (sID: integer, cName: string)

And I need to write a bunch of check constraints, and one in particular is kicking my rear end. It needs to make sure faculty in different departments can never teach in the same room; ie each room is specific to each department. I have no idea where to start, the logic is evading me. Anybody able to point me in the right direction?

# ? Mar 3, 2009 22:23

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Azerban posted:

So I've got a schema that looks like this;

Student (sID: integer, sName: string, major: string, year: string)
Class (cName: string, meets_at: time, roomno: string, fID: integer)
Faculty (fID: integer, fName: string, deptID: integer)

with a relationship;

Enrolled (sID: integer, cName: string)

And I need to write a bunch of check constraints, and one in particular is kicking my rear end. It needs to make sure faculty in different departments can never teach in the same room; ie each room is specific to each department. I have no idea where to start, the logic is evading me. Anybody able to point me in the right direction?

Something like this? (untested)

code:

ALTER TABLE Rooms 
  ADD DeptID integer NOT NULL, 
  ADD CONSTRAINT Rooms_Dept_FK FOREIGN KEY(DeptID) REFERENCES Departments(DeptID);

# ? Mar 3, 2009 22:49

Azerban: Oct 28, 2003

camels posted:

Something like this? (untested)

code:

ALTER TABLE Rooms 
  ADD DeptID integer NOT NULL, 
  ADD CONSTRAINT Rooms_Dept_FK FOREIGN KEY(DeptID) REFERENCES Departments(DeptID);

I have no table Rooms, it's based purely off the attribute roomno in the table Class.

# ? Mar 3, 2009 22:51

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Azerban posted:

I have no table Rooms, it's based purely off the attribute roomno in the table Class.

Can you add one?

# ? Mar 3, 2009 22:57

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 21:37

Azerban: Oct 28, 2003

camels posted:

Can you add one?

Unfortunately, no. It's completely possible that this constraint is impossible to do, though.

# ? Mar 3, 2009 23:01

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »